| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in one column into multiple columns
I have a workbook of date that I need to convert into a Pivot table/chart.
However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
hi
if i understand, you want to convert a column of data to a row of data? if so then.... select the column. on the toolbaredit copy then Editpastespecialtranspose If your column is more that 256 rows, you will get an error. if so, then transpose the column is sections less than 256 rows. hope this helped regards FSt1 "Flaminstar" wrote: I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
Hi....Thanks for your response however that is not what I'm trying to
achieve. When I do a tranpose for say 200 rows...it spreads it all of it across one row. What I want to do is like this... Take 'Project: xxx' in column A and move the 'xxxx' part to column b Take 'Task: xxx' in column A and move the 'xxxx' part to column c Take 'Activity: xxx' in column A and move the 'xxxx' part to column d Take 'Resource: nnnnn' in column A and move the 'nnnnn' part to column e Thanks "FSt1" wrote: hi if i understand, you want to convert a column of data to a row of data? if so then.... select the column. on the toolbaredit copy then Editpastespecialtranspose If your column is more that 256 rows, you will get an error. if so, then transpose the column is sections less than 256 rows. hope this helped regards FSt1 "Flaminstar" wrote: I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
On Sep 10, 2:10 pm, Flaminstar
wrote: Hi....Thanks for your response however that is not what I'm trying to achieve. When I do a tranpose for say 200 rows...it spreads it all of it across one row. What I want to do is like this... Take 'Project: xxx' in column A and move the 'xxxx' part to column b Take 'Task: xxx' in column A and move the 'xxxx' part to column c Take 'Activity: xxx' in column A and move the 'xxxx' part to column d Take 'Resource: nnnnn' in column A and move the 'nnnnn' part to column e Thanks "FSt1" wrote: hi if i understand, you want to convert a column of data to a row of data? if so then.... select the column. on the toolbaredit copy then Editpastespecialtranspose If your column is more that 256 rows, you will get an error. if so, then transpose the column is sections less than 256 rows. hope this helped regards FSt1 "Flaminstar" wrote: I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout If the ":" after the Project/Task label you could just use something like the following: =RIGHT("Your Text",LEN("Your Text")-FIND(":","Your Text")) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
Hi.... Yes, I've been trying that but keep getting #Value errors...
Thank you.... "Ferris" wrote: On Sep 10, 2:10 pm, Flaminstar wrote: Hi....Thanks for your response however that is not what I'm trying to achieve. When I do a tranpose for say 200 rows...it spreads it all of it across one row. What I want to do is like this... Take 'Project: xxx' in column A and move the 'xxxx' part to column b Take 'Task: xxx' in column A and move the 'xxxx' part to column c Take 'Activity: xxx' in column A and move the 'xxxx' part to column d Take 'Resource: nnnnn' in column A and move the 'nnnnn' part to column e Thanks "FSt1" wrote: hi if i understand, you want to convert a column of data to a row of data? if so then.... select the column. on the toolbaredit copy then Editpastespecialtranspose If your column is more that 256 rows, you will get an error. if so, then transpose the column is sections less than 256 rows. hope this helped regards FSt1 "Flaminstar" wrote: I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout If the ":" after the Project/Task label you could just use something like the following: =RIGHT("Your Text",LEN("Your Text")-FIND(":","Your Text")) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
i have an add-in that may help. give me an address and i'll email it.
-- Gary "Flaminstar" wrote in message ... I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
Hi Again.... I've got something working. I have to copy it to every cell,
but it works so far.... Here's what I used....=MID(A86,1,FIND(":",A86,1)-1) Thanks for your help...you pointed me in the right direction... "Flaminstar" wrote: Hi.... Yes, I've been trying that but keep getting #Value errors... Thank you.... "Ferris" wrote: On Sep 10, 2:10 pm, Flaminstar wrote: Hi....Thanks for your response however that is not what I'm trying to achieve. When I do a tranpose for say 200 rows...it spreads it all of it across one row. What I want to do is like this... Take 'Project: xxx' in column A and move the 'xxxx' part to column b Take 'Task: xxx' in column A and move the 'xxxx' part to column c Take 'Activity: xxx' in column A and move the 'xxxx' part to column d Take 'Resource: nnnnn' in column A and move the 'nnnnn' part to column e Thanks "FSt1" wrote: hi if i understand, you want to convert a column of data to a row of data? if so then.... select the column. on the toolbaredit copy then Editpastespecialtranspose If your column is more that 256 rows, you will get an error. if so, then transpose the column is sections less than 256 rows. hope this helped regards FSt1 "Flaminstar" wrote: I have a workbook of date that I need to convert into a Pivot table/chart. However, the headings that I need to sort on are all listed in column A. I need to split this column into multiple columns for sorting purposes. Each text stream in column A is starts with a "xxxxx :" ...where xxxx is 'resource', 'project', 'task', 'phase', 'activity'. Any ideas would be greatly appreciated. Here is a sample of the column.... Resource: Alex S 1 FTE Project: Genesis Task: Test Director Setup Project: Rating Task: Delivery Project: Statistical Plan Phase: Execution Phase Project: Claims Activity: Initiate / Plan / Execute / Closeout If the ":" after the Project/Task label you could just use something like the following: =RIGHT("Your Text",LEN("Your Text")-FIND(":","Your Text")) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
splitting text in one column into multiple columns
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Splitting Text from single cell in column across multiple Columns | Excel Worksheet Functions | |||
| Splitting one column into multiple columns | Excel Worksheet Functions | |||
| splitting 1 column of data into multiple columns | Setting up and Configuration of Excel | |||
| Splitting text in one column into two (or more) columns. | Excel Worksheet Functions | |||
| splitting text to multiple columns | Excel Discussion (Misc queries) | |||
Linear Mode
