Separating First and Last Names 1

[O2BNSV]

I have an excel sheet I need to prepare for import into my database. The sheet has only 1 column "Name" in which both first and last name appear such as John Smith. Is there a formula I can use to de-concatenate these so that I can have two columns: "First Name" and "Last Name"? Please advise.

 

[bluedragon2]

There are many threads in this forum that include this issue, I suggest searching for them.

Separating names can be a very tricky issue with names that do not form to just one first name and one last name.

If all of your names fell into that category, then it would be simple.


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

If all of your criteria fall into the one first and last name then this will work for you:

Last Name: =MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
First Name: =LEFT(A1,FIND(" ",A1)-1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cndjoker]

If there is a space between the first and last name in the single column name wouldn't it be easier to use the Text to Columns command in the Data menu. Select the Delimited bullet; Go to the next screen & select the Space box; & then press finish in the next screen. Make sure that you leave an extra column for the name split before doing the text to column command.

 

[JeffersonH]

You don't specify the database, but you could use database transformation service DTS in MS SQL to convert your data and prep for any other database including Oracle. Just export the spreadsheet to tab delimited, run DTS against it, and that will let you accomplish the manipulation and the import in one place.

tmp.Worldwide
Business Objects 5i
Systems Integrator
Pfizer Global Research and Development