Hi there...
I am a newbie!! I have a problem that I am trying to get around. I have a list of names of clients and their full name appears in one cell. The format is
Smith, John.
I want it to appear in two cells as first name and last name. Anyone any ideas as to how I can do this?
Frank
Select the Column
Ctrl+H
Find: , Replace with:
Data Tab->Text To Column
Delimited, Choose Space
Hit Okilly Dokilly
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Yes, you look for the comma and split it at that point. So assuming the combined name is in A2 (and that the full-stop isn't part of it), put this in B2:
=RIGHT(A2,LEN(A2)-FIND(",",A2)-1)
and this in C2:
=LEFT(A2,FIND(",",A2)-1)
then copy down.
Hope this helps.
Pete
If there is ALWAYS a comma in the data separating the surname and forename then
for surname
=LEFT(A1,FIND(",",A1)-1)
for forename
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
If the comma may or may not be present, this will take care of both situations:
With the names in column A starting in A1 enter this formula in B1 and fill down:
Formula:
Please Login or Register to view this content.
Enter this in C1 and fill down:
Formula:Please Login or Register to view this content.
John, Smith John Smith Harry Houdini Harry Houdini Adam, Jones Adam Jones Sue Allenby Sue Allenby
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
a great assortment of ways to do the job...Thank you all so much, I truly appreciate it.
Frank
a great assortment of ways to do the job...Thank you all so much, I truly appreciate it.
Frank
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks