Separating Text in a cell (Excel XP)

Topic

New Reply

We have a database that has a first name and a last name in once cell, separated by a comma. Is there any formula that will let us separate the first name and last name into two separate cells. I thought of using left or right but the number of characters in the first and last names is not consistent.

Thanks in advance for your help.

Christa

Reply | Quote

Replies

The following macro will split names. Paste the code into a module, select the cells with the names, then run the macro.

Sub SplitOnComma()
On Error GoTo Exit_Sub
Dim aCell As Range
‘ Select one column
Selection.Columns(1).Select
‘ Insert a column
Selection.Offset(0, 1).Insert xlShiftToRight
‘ Splits cells
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1))
‘ Remove superfluous spaces
For Each aCell In Selection
aCell = Trim(aCell)
aCell.Offset(0, 1) = Trim(aCell.Offset(0, 1))
Next
Exit_Sub:
Set aCell = Nothing
End Sub

Reply | Quote

(Edited by sdckapr on 29-Jul-05 15:50. added PS)

If A1 has the text like “Last, First”

Then this will give the last name:

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

And this will give the first:

=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)

Steve
PS Data – Text to columns can also parse it without code or formulas

Reply | Quote