seperate data from one cell into many....

[Guest]

I have an error log coming through which i want to seperate each of the
errors into there own cell on the same row. and at the same time i want each
of the columns to only have the same error codes. example below of 2 rows of
errors

the errors are sperated by ; which i can then use text to columns but this
just seperates them out but does not sort them into be the same columns

example

row 1 orange; apples; pineapples; carrotts, lettuce
row 2 apples; carrotts; potatoes


to then be sorted into the following cells

row 1 orange apples pineapples carrotts lettuce
row 2 apples carrotts potatoes


sorry about the exmple and thanks for the help
pete

 

[Guest]

If the source data is in field order, then see if you can force it to insert
a ";" in a blank field. Doing so should pad the fields to the same number of
values per row.

What application generates the error log?

P.

 

[Dave Peterson]

This may work for you.

I put the data in A2:A9999
I put this formula in B2:B9999
=";"&SUBSTITUTE(A2," ","")&";"
This removed the spaces and added semicolons around the whole string:
;orange;apples;pineapples;carrotts,lettuce;


Then I put the words I wanted in C1:F1
And put this formula in C2:
=IF(COUNTIF($B2,"*;"&C$1&";*")>0,C$1,"")
and dragged across to F2 and down the rows.