Split cells into multiple columns by carriage return

**taylorcharley** · 11-12-2016, 07:29 AM

My data has multiple rows in one cell seperated by carriage return. I want to put each line of the cell into a new column. I have tried using Text to Columns with the Other Option under Delimiters being Ctrl + J. It only shows the first line of the cell. The subsequent lines of each cell are deleted.

For example

UNIT 1/1 SAMANTHA CRT
BOXFIELD
MELBOURNE

becomes

UNIT 1/1 SAMANTHA CRT

BOXFIELD & MELBOURNE just disappear completely instead of moving to next column.

**Tony Valko** · 11-12-2016, 07:44 AM

Maybe use a formula...

Data Range

	A	B	C	D
1	UNIT 1/1 SAMANTHA CRT BOXFIELD MELBOURNE	UNIT 1/1 SAMANTHA CRT	BOXFIELD	MELBOURNE

This formula entered in B1 and copied across:

=TRIM(MID(SUBSTITUTE(CHAR(10)&$A1&CHAR(10),CHAR(10),
REPT(" ",255)),COLUMNS($B1:B1)*255,255))

**taylorcharley** · 11-12-2016, 07:49 AM

THANK YOU! That has done it!

**Tony Valko** · 11-12-2016, 07:59 AM

Good deal. Thanks for the feedback!

Split cells into multiple columns by carriage return

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Split cells into multiple columns by carriage return

Re: Split cells into multiple columns by carriage return

Re: Split cells into multiple columns by carriage return

Re: Split cells into multiple columns by carriage return

Thread Information

Users Browsing this Thread

Similar Threads

multiple cells split to multiple rows with other columns copying their info down

Excel concatenate and carriage return by columns

Carriage Return multiple entries one cell

How do merge two cells with a carriage return in between the two v

Join 2 cells with carriage return in between

[SOLVED] Text to columns delimited by carriage return

[SOLVED] How do I convert text to columns when there is a carriage return?

Bookmarks

Bookmarks

Posting Permissions