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

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
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! :unsure: ...plus this will need changing as new people come and go all the time!

Many Thanks
Justin
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
If your data starts in A1, enter the following formula and drag down as far as necessary...

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

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F8)

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

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

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
njimack - yours say #VALUE!

and

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

sorry...
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
njimack - yours say #VALUE!

Strange - works for me (see below).
Book1
ABCD
1jones,keith41256keithjones
Sheet1
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Neil works fine for me too using it on a sheet of mine right now (",)
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Neil works fine for me too using it on a sheet of mine right now (",)
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If the original text has extra leading or trailing spaces, (or invisible characters), that would throw off some formulas.
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top