Split data from one column into two columns

[Jonni Anderson]

I have a large database where the first and last names are in one column. I need to have them in separate columns. Is there a way to separate the names without having to cut and paste?

Thanks.

[#2 · Accepted solution]

Hi Jonni,

This is the best technique to split any column based on a delimiter (Space, Comma...)

 

[#3]

Thanks, Haythem!

But that only works if the first name and last name in every case are exactly the same length, as they are in your example. My database is composed of names of different lengths.

I tried your method, and wound up with some names being truncated, and in others part of the last name remained in the firstname column.

Jonni

[#4]

Hi Jonni,

Could you please provide us with a sample of your database to diagnose it and to provide you with the perfect solution?

[#5]

Greetings, Haytham!

See attached. You'll notice some of the names are first names only.

Thanks for your help!

Jonni

[#6]

Jonni,

I've tested the solution on your data, but it works just fine!
Every name the column A have two words (first name and last name) delimited by space is split into two cells.
If the name has a single word (first name), it stayed in place!

The solution depends on the space as a delimiter between the first name and the last name, regardless of the length of the name.

If the name has more than one space or was in reversed order (Last Name First Name), the technique will not work!
Same thing if there is no space between the first name and the last name or if the first name or last name is consist of more than one word.

[#7]

I owe you an apology! I didn't follow your steps exactly; I missed the part about choosing the delimiter; I clicked "fixed width" instead.

Thank you very much!