i am trying to do text to column (see example below)
John De Santos.
How do I separate the names, but keep De Santos (as is) in one column.
Hi aelspeth and welcome to the forum,
This is an age old problem. If you are delimiting a string by spaces and "De Santos" has a space in the last name, it will end up in two different columns. The same thing happens with trying to Proper() names like McIntire. It will come out as Mcintire. If it were me, I'd take the space out of the last name and make it DeSantos using Search and Replace.
I'm sorry but there is no good/generalized answer.
One test is worth a thousand opinions.
Click the * below to say thanks.
If you only ever have 'first name' 'last name' then you can do it with formulas by splitting on the first space. If there is no consistent format, then you may have to use TTC and then repair those that have multiple words for the last name.
Good luck.
Hi,
if you have ...
A1 ... John De Santos
B1 ... =LEFT(A1;SEARCH(" ";A1)-1) ... John
C1 ... =RIGHT(A1;LEN(A1)-SEARCH(" ";A1)) ... De Santos
... probably you'll need to replace ";" with ",".
Cheers, Marko
What if some of the names have middle names or middle initials?
John J Doe
Bill Mac Intoch
Billy Joel Smith
Prince
One test is worth a thousand opinions.
Then answare is ...Originally Posted by MarvinP
... or check and correct results.
Cheers, Marko
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks