Convert Text to Columns (Cells Include Line Breaks)

littlefish

New Member
Joined
Mar 6, 2008
Messages
13
Office Version
  1. 365
Platform
  1. 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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. 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
53,317
Office Version
  1. 365
Platform
  1. Windows
Select semi-colon, comma & other and in the box for other type Ctrl J
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
  1. 365
Platform
  1. 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
53,317
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top