Delimiter result not the formula

[EZ1975]

I have a string that was exported from another program.
It has quotation marks around it so I cannot do a text to columns on it
Below is one cell.

"PROBLEM: LOAD ERROR, WILL NOT CHARGE
SOLUTION: REPLACE MAINBOARD, BARCODE AND PIN FIX, REPLACE IO
LEVEL 2: BILL COMPANY, 201080354, 30160045
REPAIR EZ
"


I need to extract into separate into columns the problem, solution, the level number, and all the numbers that follow company.
I can do a clean on the cell but then I can't use text to columns because it shows a formula.
Any help is appreciated

 

[Rick Rothstein]

Two choices, either one should work...

1) Use Excel's Replace dialog on the column to replace the quotes with nothing and then use Text To Column (only set CTRL+J in the Other field, leave everything else unchecked) on the column

2) Use Text To Columns twice on the column (only set CTRL+J in the Other field, leave everything else unchecked)... the first time will remove the quotes, then the second time will split out the delimited values)

 

[EZ1975]

Thank you, I forgot to mention the quote marks are hidden, so a find and replace does not see them and it looks like they also break text to columns. Text to columns only lets me pull the first line out of the cell.

 

[Fluff]

With Text to Columns on page 2 set Text Qualifier to {none}

 

[EZ1975]

I found a very roundabout way of doing what I needed. =CLEAN(SUBSTITUTE(A2, CHAR(10),";")) cleaned up the cell and then I was able to copy and paste the values. After that I was able to use text to columns.
Thanks for the help!