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).

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies