Delimiter result not the formula

EZ1975

New Member
Joined
Jul 26, 2018
Messages
5
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
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)
 
Last edited:

EZ1975

New Member
Joined
Jul 26, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
With Text to Columns on page 2 set Text Qualifier to {none}
 

EZ1975

New Member
Joined
Jul 26, 2018
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,141
Messages
5,466,917
Members
406,507
Latest member
annemiranda8

This Week's Hot Topics

Top