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
P5 Campws Penglais
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.
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.