Split Sort cell values in Excel - either Macro or VBA

Hello All,

I have thousands of cells with the following name in this format in each cell:

(A)
surname, firstname

This is the general format and the first and surname is always separated by a comma.

Can I switch and actually separate these out so I have firstname in one cell and surname in another - something like this?

(B)             (C)
Firstname  Surname


It would be great to have either as a macro or vba script I can run?

Thanks in advance - GISVPN
gisvpnAsked:
Who is Participating?
 
Naresh PatelTraderCommented:
You can achieve this by formula too. assuming your name is in cell A3 then formula in
B3for Name =MID(SUBSTITUTE(A3," ",""),FIND(",",SUBSTITUTE(A3," ",""),1)+1,100)


For Surname Formula in Cell C3=LEFT(SUBSTITUTE(A3," ",""),FIND(",",SUBSTITUTE(A3," ",""),1)-1)

Thanks
0
 
Dan CraciunIT ConsultantCommented:
Why don't you simply use Text to Columns? It will do what you want, no code necessary.
text to columns
HTH,
Dan
0
 
Naresh PatelTraderCommented:
Formula is amended so if you tried with older one then it may produce wrong output, so pls try amended one.

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gisvpnAuthor Commented:
Dan - thanks for the suggestion - worked a treat. I had no idea there was this feature in Excel.

itjockey - I tried the formula also but it did not seem to work as expected.
0
 
gisvpnAuthor Commented:
Oh sorry  itjockey - we pressed submit at the same time, thanks will try the updated one.
0
 
Saqib Husain, SyedEngineerCommented:
Here is a macro

Sub lastto1st()
Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeConstants).Offset(, 1).FormulaR1C1 = "=replace(rc[-1],1,find("","",rc[-1]),"""")"
Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeConstants).Offset(, 2).FormulaR1C1 = "=replace(rc[-2],find("","",rc[-2]),9999,"""")"
Range("B2:C" & Rows.Count).Value = Range("B2:C" & Rows.Count).Value
End Sub
0
 
Naresh PatelTraderCommented:
itjockey - I tried the formula also but it did not seem to work as expected.
You tried mine? & it is not produce result as expected ?
then why you accept my solution :)
 
in my formulas i had limits the name to 100 character if there is more then 100 character for name then try this for name (Assuming text string in A3) Cell B3=MID(SUBSTITUTE(A3," ",""),FIND(",",SUBSTITUTE(A3," ",""),1)+1,LEN(A3))

and for Surname  Cell C3 =LEFT(SUBSTITUTE(A3," ",""),FIND(",",SUBSTITUTE(A3," ",""),1)-1)

or provide me sample file i do it on your behalf.
Thanks
0
 
Dan CraciunIT ConsultantCommented:
Yeah, the OP said my solution works and yours doesn't and then accepts your solution :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.