How to chnage this 'jones, keith 41256' to 'Kieth Jones'

[hey yer barred]

Hi All!

I have over 500 names in a list and i need them in a normal name format...they currently look like this -

jones, keith 41256

I want them like this for reporting reasons

Keith Jones

On every name, there will always be a 5 digit number at the end and always a ',' sperating the last name from the first name.

Is there some sort of function in excel that will do this for me, as I really dont want to type all 500 names! ...plus this will need changing as new people come and go all the time!

Many Thanks
Justin

 

[njimack]

If your data starts in A1, enter the following formula and drag down as far as necessary...

=MID(A1,FIND(", ",A1)+2,LEN(A1)-FIND(", ",A1)-7)&" "&LEFT(A1,FIND(", ",A1)-1)

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F8)

Sub Names()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
d = Cells(a, 1)
b = InStr(Cells(a, 1), " ")
c = InStrRev(Cells(a, 1), " ")
Cells(a, 2) = Mid(d, b + 1, c - b - 1) & Left(d, b - 1)
Next a
End Sub

run the macro. It will rearrange the names into col B
ravi

 

[Jonmo1]

Try

=LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND(" ",REPLACE(A1,1,FIND(" ",A1),""))-1) & " " & LEFT(A1,FIND(",",A1)-1)

 

[hey yer barred]

njimack - yours say #VALUE!

and

ravishankar...yours comes up with invalid arguement...

sorry...

 

[hey yer barred]

We have a winner!!! jonmo1....yours worked...cheers mate!!!

 

[njimack]

njimack - yours say #VALUE!

Strange - works for me (see below).

Book1
	A	B	C	D
1	jones,keith41256	keithjones
Sheet1

 

[ADAMC]

Neil works fine for me too using it on a sheet of mine right now (",)

 

[ADAMC]

Neil works fine for me too using it on a sheet of mine right now (",)

 

[Datsmart]

If the original text has extra leading or trailing spaces, (or invisible characters), that would throw off some formulas.