splitting text in one column into multiple columns

[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

 

[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

 

[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

 

[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"))

 

[Flaminstar]

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

Thank you....

 

[Gary Keramidas]

i have an add-in that may help. give me an address and i'll email it.

 

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

 

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