EXCEL Tips 2

'Macro to split cells at the comma

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

'Macro to join 2 cells

Sub Join()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
On Error Resume Next
Dim irows As Long, mrows As Long, ir As Long, ic As Long
irows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mrow = lastcell.Row
If mrow < irows =" mrow" icols =" Selection.Columns.Count" ir =" 1" newcell =" Trim(Selection.Item(ir," ic =" 2" trimmed =" Trim(Selection.Item(ir,"> 0 Then newcell = newcell & " " & trimmed
Selection.Item(ir, ic) = ""
Next ic
Selection.Item(ir, 1).Value = newcell
Next ir
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub