Splitting columns
Posted by Amy Oh on January 12, 2000 12:15 PM
On a Excel spreadsheet, with a column containing a lastname, firstname , how would I separate these into individual colums with one containing last name and the other containing first name?
Posted by Celia on January 12, 2000 4:13 PM
Amy
Assuming the lastnames and firstnames are separated by a comma, the following macro will split the selected fullnames into the next two columns to the right
Celia
Sub SplitName()
Dim cell As Range
Dim separator As Integer
'loop through each cell in the selection
For Each cell In Selection
'search for a comma
separator = InStr(cell.Value, ",")
If separator > 0 Then
'put the lastname in the first column to the right
cell.Offset(0, 1).Value = Left(cell.Value, separator - 1)
'put the firstnames in the second column to the right
cell.Offset(0, 2).Value = Mid(cell.Value, separator + 1)
End If
Next cell
End Sub
Posted by LOMAN (David) on January 12, 2000 5:06 PM
Posted by Celia on January 13, 2000 10:42 PM
Amy
Have found a non-macro way of splitting column data that is separated by a comma.
Formula to extract characters before the comma :-
=LEFT(A1,LEN(A1)-FIND(",",A1))
Formula to extract characters after the comma :-
=RIGHT(A1,LEN(A1)-FIND(",",A1))
Celia
Posted by Celia on January 13, 2000 11:37 PM
CORRECTION
Sorry, the formula for the characters before the comma should be :-
=LEFT(A1,FIND(",",A1)-1)
