advertisement
Forums

The Forum is sponsored by 
 

AAPL stock: Click Here

You are currently viewing the Tips and Deals forum
Numbers question: entering formula to extract first name from full-name field
Posted by: dmag
Date: June 07, 2014 07:00PM
Hi,

I have a list of customers in a CSV file. The way it was created, the full name is entered in a single cell; like John Smith.

When I try to load it up into an emailing program and create a greeting, it comes out "Hello John Smith," not "Hello John." Thus, I need to use a spreadsheet program to extract the first names out of the full names, and enter them into a new column. Then, I can import them.

I use Numbers, though I have the free Neo Office.

I went to an Apple support forum and got the following formula:

=IFERROR(LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1),IF(ISBLANK(B2),"",TRIM(B2)))

I'm not very good with spreadsheets, so I'm not sure how to enter this formula in Numbers to bring about the desired change.

If you can, please give me the steps to get this formula in to give it a try.

Thanks.
Options:  Reply • Quote
Re: Numbers question: entering formula to extract first name from full-name field
Posted by: Filliam H. Muffman
Date: June 07, 2014 07:30PM
Luckily LibreOffice is close enough to Neo that the formula works with no modification.

copypasta the formula into a column where you want the first name to appear. Paste the full name data into column B, starting at line 2. Use the Fill Down function extend the formula column down to the end of the full name column. It should now show the first names. If the mail merge program does not like the input from the formula, Copy the formula column, and select [Paste Special ->Text] into another column that you will use for the mail merge.



In tha 360. MRF User Map
Options:  Reply • Quote
Re: Numbers question: entering formula to extract first name from full-name field
Posted by: Don C
Date: June 07, 2014 09:33PM
It is always a good idea to check the output of the formula on ALL the names, though. Sort the results and scan down the names. An extra space or a missing space in the combined field will result in strange results and you want to correct those before sending anything out. Trust but Verify ...
Options:  Reply • Quote
Re: Numbers question: entering formula to extract first name from full-name field
Posted by: TheTominator
Date: June 07, 2014 10:13PM
I think the formula you found is incorrect for Numbers (at least it is incorrect in my version Numbers '08 v1.0.3)
There is no IFERROR() function.

There is an IFERROR() function and there is an IF() function.

I think the formula could be
=IF(ISERROR(SEARCH(" ",TRIM(B2))),TRIM(B2),LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1))

If your Firstname Lastname is in column B
Copy that line.
Click into a cell in a different column such as column C in row 2.
Paste.
Click on the green checkmark.


Note that the "LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1)" part does the trick of converting "John Smith" into "John". The rest of it is to accommodate entries that don't have multiple parts separated by a space. This might be people or business names with only one word names (e.g. "Cher" or "Sting" or "Apple").


To copy that formula to all of the cells in a column, use the "fill down" feature.



Edited 1 time(s). Last edit at 06/07/2014 10:17PM by TheTominator.
Options:  Reply • Quote
Re: Numbers question: entering formula to extract first name from full-name field
Posted by: dmag
Date: June 08, 2014 01:27AM
Thanks! Worked perfectly.

I appreciate everybody's input. This saved me a ton of time.

Have a great Sunday, folks!



Quote
TheTominator
I think the formula you found is incorrect for Numbers (at least it is incorrect in my version Numbers '08 v1.0.3)
There is no IFERROR() function.

There is an IFERROR() function and there is an IF() function.

I think the formula could be
=IF(ISERROR(SEARCH(" ",TRIM(B2))),TRIM(B2),LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1))

If your Firstname Lastname is in column B
Copy that line.
Click into a cell in a different column such as column C in row 2.
Paste.
Click on the green checkmark.


Note that the "LEFT(TRIM(B2),SEARCH(" ",TRIM(B2))-1)" part does the trick of converting "John Smith" into "John". The rest of it is to accommodate entries that don't have multiple parts separated by a space. This might be people or business names with only one word names (e.g. "Cher" or "Sting" or "Apple").


To copy that formula to all of the cells in a column, use the "fill down" feature.
Options:  Reply • Quote
Sorry, only registered users may post in this forum.

Click here to login

Online Users

Guests: 108
Record Number of Users: 186 on February 20, 2020
Record Number of Guests: 2330 on October 25, 2018