Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to separate first and last names from one cell to two | Excel Discussion (Misc queries) | |||
Names and Separate Worksheets | Excel Worksheet Functions | |||
cell designation | Excel Discussion (Misc queries) | |||
Invisible Text designation in cell? | Excel Worksheet Functions | |||
How I can set highlighted active cell in row & colmun cross | Excel Discussion (Misc queries) |