Extracting Data from a Cell

[sassy]

Trying to figure out a solution to the following problem.

I have a cell that uses Concatenate to combine my data:


D1
=IF(HE="Fail",CONCATENATE(IF(I25="", "", I25 & ","), IF(I34="", "", I34 & ","), IF(I45="", "", I45 & ","), ,IF(I59="", "", I59 & ","),"None")

I am trying to come up with a formula or sub routine that will extract and separate the data from D1 into various cells. Let's say A10:A18. I have tried using the 'Text to Columns' by commas, but it is breaking up the formula and not the calculated data. I have also tried MID, Left and Right, but at times, I have more than three. The data from the "I" Column varies in size but is a drop down of about 25 choices.

 

[ango858]

Trying to figure out a solution to the following problem.

I have a cell that uses Concatenate to combine my data:


D1
=IF(HE="Fail",CONCATENATE(IF(I25="", "", I25 & ","), IF(I34="", "", I34 & ","), IF(I45="", "", I45 & ","), ,IF(I59="", "", I59 & ","),"None")

I am trying to come up with a formula or sub routine that will extract and separate the data from D1 into various cells. Let's say A10:A18. I have tried using the 'Text to Columns' by commas, but it is breaking up the formula and not the calculated data. I have also tried MID, Left and Right, but at times, I have more than three. The data from the "I" Column varies in size but is a drop down of about 25 choices.

if these are in a table you can add a column that =D1 (this will give you the value rather than the formula)
then you can break that cell apart by the commas

 

[ango858]

Or just paste--> special --> values to give you the value rather than the formula and break that apart.

 

[Rick Rothstein]

Trying to figure out a solution to the following problem.

I have a cell that uses Concatenate to combine my data:

D1
=IF(HE="Fail",CONCATENATE(IF(I25="", "", I25 & ","), IF(I34="", "", I34 & ","), IF(I45="", "", I45 & ","), ,IF(I59="", "", I59 & ","),"None")

I am trying to come up with a formula or sub routine that will extract and separate the data from D1 into various cells. Let's say A10:A18.

If I understand your question correctly, why not just copy/paste the individual formulas that you concatenated together into the cells you parts to be located in?

 

[sassy]

ok, the Paste Special > Values worked. Is there a way that I can make it split by rows instead of columns??? The user will be using this as sort of a check list

Also, to answer your question Rick, I am using CONCATENATE because I was trying to find a way to remove all the blanks. It CONCATENATE's about 15 cells, and any 11 of them are always blank. This is the only way I could think of to make it work, because I then need those 4 that are filled in to feed into another column without a bunch of blanks. Unless you can think of another way to get to that same end result.