Convert Text to Columns (Cells Include Line Breaks)

littlefish

New Member
Joined
Mar 6, 2008
Messages
13
Office Version
365
Platform
Windows
I have a single column of several hundred rows of data that I would like to use the "convert text to columns" wizard, however, in addition to typical delimiters (i.e. "," and ";") these cells contain line breaks. When I run the wizard, the result is a single column of data to the point of the line break. Each individual cell has varying counts of delimiters.

I would welcome input on how to split the data into multiple columns (it's possible in some cases there could be up to eight columns needed,) accounting for three delimiters:
,
;
[line breaks]

Thanks so much in advance.

TZ
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,559
Office Version
2019
Platform
Windows
Use the XL2BB to provide a sample of your data so that we can see exactly what you have. Make sure also for the sample data to show us what your expected results will look like.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,606
Office Version
365
Platform
Windows
Select semi-colon, comma & other and in the box for other type Ctrl J
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,314
out of curiosity
what if there is more delimiters, like: comma, semi-colon, pipe & LF ? (or more)
 

littlefish

New Member
Joined
Mar 6, 2008
Messages
13
Office Version
365
Platform
Windows
This is a single cell in my xls, and the XL2BB c/p displays it with line breaks:

LA PLATA CO CO .1875 MI IN THE S/2 SW/4 O/O 320 ACRE W/2 SPACING IN SEC. 17 T32N-R6W; IGNACIO BLANCO (FC) ABEYTA NO. 17-1; .0140625 NRI



After applied delimiters, this cell would appear in six consecutive columns:

LA PLATA CO CO .1875 MI IN THE S/2 SW/4 O/O 320 ACRE W/2 SPACING IN
SEC. 17 T32N-R6W
IGNACIO BLANCO
(FC)
ABEYTA NO. 17-1
.0140625 NRI
 

littlefish

New Member
Joined
Mar 6, 2008
Messages
13
Office Version
365
Platform
Windows
Select semi-colon, comma & other and in the box for other type Ctrl J
I was in the midst of pasting the sample data when you responded. It came out perfect. Thank you so much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,606
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,005
Messages
5,465,959
Members
406,456
Latest member
jmishra91

This Week's Hot Topics

Top