separate names and designation from one cell into 2 colmun

K

Khoshravan

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.
 
S

Stefi

Have a look at Data>Text to columns (separated by semicolom)

Regards,
Stefi

„Khoshravan†ezt írta:
 
K

Khoshravan

To complete my question, I should add that whole text in cell A1 has 17840
characters (Len(A1)). Also there might be some spaces between names, but the
exact format which is same through all names is "-"between name and
designation and ";" between a pair of names and designations.
 
D

David Biddulph

So use Data Text to Columns with semi-colon as delimiter.
Copy that row then Edit/ paste special/ transpose to paste into a column.
Data Text to columns on the new column range with hyphen as delimiter.
 
K

Khoshravan

I used Data|text to columns and succeeded few steps. There are few problems
as follows:
1- It gives the result in one row not in 2 columns. how can I fix this?
2- Number of entries goes beyond the number of columns limit and some data
is lost. It is not easy to find the truncated location and try again.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
D

David Biddulph

If you are starting with a text file you may be better starting the process
in a text editor, changing semi-colons to paragraph marks or something which
will give a new row when you pull it into Excel.
Then you can use Data/ Text to Columns to split at the hyphen.
 
K

Khoshravan

Dear Ron

Thank you very much for putting your time to my question.
For problem in my hand, I solved it with previous posts (Data| Text to
columns) but I will check your Macro later and tell you what happened.
 
Top