Home

Text to columns

Axle-Flipps_1
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

Aberystwyth

Ceredigion

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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies