Split first and last name formula

I have a column of cells with names in the format John, Smith. I need to split them into first name and second name?

By Neale Blackwood

Assuming the name is in cell A1 the following formulae will extract the first and second names:

First name formula =LEFT(A1,SEARCH(“,”,A1)-1)

Second name formula =TRIM(RIGHT(A1,LEN(A1)-SEARCH(“,”,A1)))

You could also use Excel’s ‘Text to Columns’ feature in the Data menu. This allows you to split the contents of cells based on commas, spaces, semi-colons or another character that you specify.

In the above example you would select the range that had the names then click the Data menu, select ‘Text to Columns’, ensure the Delimited option is selected and click ‘Next’. Select the ‘Space’ and ‘comma’ options and Click ‘Next’. Select the ‘Space’ and ‘Comma’ options and Click ‘Next’ and ‘Next’ again.

Extras

When using the Text To Columns feature it is a good idea not to have entries in cells to the right of the column being split as they may be overwritten.

In Excel 2007 the Text To Columns icon is in the Data Ribbon tab in the Data Tools section.