Text to Columns

[Me at Home]

Anyone out there suggest a way of splitting names. i.e

Mr Anthony Lee Rodgers
Mr John Edward Rourke
Mr Ian Ruff
Mr Ian Sanderson

I am looking to get the following end result.
Title 1st Name(s) Last Name
Mr Anthony Lee Rodgers
Mr John Edward Rourke
Mr Ian Ruff
Mr Ian Sanderson


Regards

 

[Gary''s Student]

Select the cells you want to parse and run this macro:

Sub NameDropper()
For Each r In Selection
s = Split(r.Value, " ")
r.Offset(0, 1).Value = s(0)
r.Offset(0, 3).Value = s(UBound(s))
If UBound(s) = 2 Then
r.Offset(0, 2).Value = s(1)
Else
r.Offset(0, 2).Value = s(1) & " " & s(2)
End If
Next
End Sub

The macro will fill the cells to the right of the selected cell and leave
the original data alone. It will combine first and middle names (if there is
a middle name).


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Me at Home]

Thanks for that. Ran the macro got run time error 9.

Any thoughts?

 

[muddan madhu]

col A has names

B1 put this formula =LEFT(A1,FIND(" ",A1)-1) and drag it down
C1 put this formula =MID(A1,LEN(B1)+1,LEN(A1)-LEN(D1)-2) and drag it
down
D1 put this formula =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"
","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) & drag it down