splitting text in one column into multiple columns

F

Flaminstar

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
 
F

FSt1

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 toolbar>edit> copy
then Edit>pastespecial>transpose

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
 
F

Flaminstar

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
 
F

Ferris

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

If the ":" after the Project/Task label you could just use something
like the following:

=RIGHT("Your Text",LEN("Your Text")-FIND(":","Your Text"))
 
F

Flaminstar

Hi.... Yes, I've been trying that but keep getting #Value errors...

Thank you....
 
F

Flaminstar

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

Flaminstar

Hi...Send it to (e-mail address removed). Mine didn't work quite the way I
wanted it... I need it to write in the new column what comes after the ":"
and not before (ie: not "project")....

Thanks...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top