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

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


--
mrice

Reserach Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=535353

=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)

"Althea" wrote in message
...

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

You could use this formula for surname

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535353

hi!

try this!

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

assuming that the firstname & surname together in A1

-via135

Althea Wrote:
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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=535353

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