Splitting firstName from Surname

[Althea]

Hi
Forgotten how to split into separate columns. Have FirstName and Surname
together in one column (unfortunately some have a missle initial).
Want surname in separate column.
Tried Text to columns, but inital letter messes up the split.
Help??
Thanks
Althea

 

[mrice]

For the first name you can use

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

For the surname, a new user defined function can be used.

(Select Surname from user defined list after pasting the following to a
VBA module)


Function Surname(Cell)
For M = Len(Cell) To 1 Step -1
If Mid(Cell, M, 1) <> " " Then
Surname = Mid(Cell, M, 1) & Surname
Else
Exit For
End If
Next M
End Function

 

[Bob Phillips]

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

and

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[daddylonglegs]

You could use this formula for surname

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[via135]

hi!

try this!

in B1: =LEFT(A1,FIND(" ",A1,1))

assuming that the firstname & surname together in A1

-via135

 

[Althea]

You could use this formula for surname

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Many thanks for all the different suggestions, problem solved
Thanks
Althea