separate names and designation from one cell into 2 colmun
 

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

Have a look at DataText to columns (separated by semicolom)

Regards,
Stefi

€žKhoshravan€� ezt Ã*rta:

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Khoshravan" wrote:

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

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.
--
David Biddulph

"Khoshravan" wrote in message
...
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.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Khoshravan" wrote:

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

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. Khoshravan
Please click "Yes" if it is helpful.


"Khoshravan" wrote:

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

On Tue, 3 Mar 2009 00:23:01 -0800, Khoshravan
wrote:

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

separate names and designation from one cell into 2 colmun
 

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.
--
David Biddulph

"Khoshravan" wrote in message
...
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. Khoshravan
Please click "Yes" if it is helpful.


"Khoshravan" wrote:

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.

separate names and designation from one cell into 2 colmun
 

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.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Ron Rosenfeld" wrote:

On Tue, 3 Mar 2009 00:23:01 -0800, Khoshravan
wrote:

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