I have copied a list of employee names and their designation into Excel. All
list appear in one cell. There are about 500 names. Names are followed by
employee designation. The format is as follows:
"name-designation;name-designation" and repeats for 500 names
I want to have names in one column and designation in second column separated.
Since all of your data is in one cell, here is a VBA macro to split it up.
The VBA uses the Split function, so if you are using a very old version of
Excel, we may have to rewrite that part. Let me know.
Also, I have hard coded the source cell as being A1, and the results starting
in A2. If all goes well, you can subsequently delete A1. Also, you can easily
change those designations to whatever is appropriate for your worksheet.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.
==============================
Option Explicit
Sub SplitNameDesignation()
Dim rSrc As Range, rDest As Range, c As Range
Dim i As Long
Dim Temp As Variant
Set rSrc = Range("A1") 'or wherever data is
Set rDest = Range("A2") 'upper left cell of
'result range
Temp = Split(rSrc, ";")
'clear destination range
Range(rDest, rDest(UBound(Temp) + 2, 2)).ClearContents
For i = 0 To UBound(Temp)
rDest(i + 1, 1).Value = Split(Temp(i), "-")(0)
rDest(i + 1, 2).Value = Split(Temp(i), "-")(1)
Next i
End Sub
================================
--ron
