Text to columns

Occasional Visitor

We are migrating from one database to another. I have a customer spreadsheet with an address block which is all in 1 column but separated by line breaks that needs splitting into 6 columns. When you click the cell it looks like this:


National Centre for Learning Welsh

Aberystwyth University

P5 Campws Penglais



SY23 3UX


When I press 'text to columns' it only transfers the 1st line and ignores the rest - even in the preview.

I am using Ctrl J as the delimiter.

I have done find and replace to swap the line break for different delimiter characters (e.g. ~ ; ^) e.g.


National Centre for Learning Welsh~Aberystwyth University~P5 Campws Penglais~Aberystwyth~Ceredigion ~SY23 3U


but it still ignores everything after the first line. I've tried it with a test (test~test~test) just typed in to make sure I'm doing it correctly and that works.

2 Replies
Did you highlight the rows with data in it? If you just highlight the first row that is all you get. So highlight all the rows with data and show work fine, (You can highlight the entire column by pressing the Column letter at the top of the worksheet.

@Axle-Flipps_1 , looks strange. Could you please provide small sample Excel file to check? (info as in your post will be enough).