Best Methods to Split First Name, Last Name, and Company in Excel

Pierre L.

Whats the best way to split a column where you have the first name and the lastname and the company ?

4 comments

Channeled
APP
·
·
To split a column containing first name, last name, and company into separate fields, you can use our "Extract Data From Values" feature. Here's how: 1. Use the "Extract Data From Values" option to process the data. 2. Add new columns using the forward slash (/) command for each field you want to extract. 3. Apply a custom extraction regex: [^,"]+"?[^,"]+ This will split the values into separate columns. Once done, you can use our "write to table" feature to send the newly created columns to your desired table. Alternatively, you can use the "Merge columns" feature in reverse: 1. Click "Add Column" > "Merge columns" 2. Input a formula to separate the combined data into individual columns Remember, these methods don't require additional credits as they're working with existing data in your table.
Sources: Hide, Re-order, Sort, Filter, Merge Columns Write to Other Table | Documentation | Clay University Write to Other Table | Documentation | Clay University
Pierre L.
·
·
I’ve used a formula and I’ve this result : [“firstname”,“lastname”,“jobtitle”] How can I split this in 3 distincts columsn ?
Channeled
APP
·
·
To split your column with values like ["firstname","lastname","jobtitle"] into three distinct columns, you can use our "Extract Data From Values" feature. Here's how: 1. Use the "Extract Data From Values" option to process the data. 2. Add new columns using the forward slash (/) command for each field you want to extract (firstname, lastname, jobtitle). 3. Apply a custom extraction regex: [^,"]+"?[^,"]+ This will split the values into separate columns. Once done, you can use our "write to table" feature to send the newly created columns to your desired table. Alternatively, you can use the "Merge columns" feature in reverse: 1. Click "Add Column" > "Merge columns" 2. Select appropriate data types for each new column 3. Write formulas to extract each value from the original column These methods don't require additional credits as they're working with existing data in your table.
Sources: Hide, Re-order, Sort, Filter, Merge Columns Write to Other Table | Documentation | Clay University Write to Other Table | Documentation | Clay University
Stefano
·
·
Hi Pierre, thank you for reaching out. One free way to carry out this task, is through the use of " Formulas" enrichment. Please note that you would have to create three different formulas. In order to add a formula column, select "Add Column" and then from the drop-down menu select formulas, as shown below:
(104 kB)
After selecting this, you'll be able to either use an AI prompt in the first box or JavaScript to instruct Clay on how to process the results from a column. An example of what this looks like is shown below:
(228 kB)
In this case, I recommend using the first box to specify what you want Clay to do. For first name, you could tell it to extract the first word in the cell. For last name, tell it to return the second word in the cell. Then, click on "Generate Formula," which will create a JavaScript formula for you and show the results that would be generated if the formula were applied. If you're satisfied with the results, simply click "Output Is Correct, Save Formula." The button is shown below:
(13 kB)
For more information on how AI formulas work, I recommend you take a look at this Clay University video, which explains everything in more detail! Please let me know if this helps!