Trouble splitting text to columns

[laundon]

I have some base data where Cell A2 contains a unique number, and cell B2 contains a block of text, separated by what looks like a carriage return or line feed.

I need to, in a perfect world, split the contents of B2 across the spreadsheet.

I've tried Text to Columns and using Ctrl-J as the delimiter, but I can't seem to get this to do anything at all.

If I look at the csv file in Notepad++ I see CR and LF after every sentence.

Any advice would be greatly appreciated as I'm pulling what little hair I have left out!

Many thanks,
Tim

 

[Micron]

Have never tried TTC with line breaks, but they are usually recognized in one of three ways, depending on the use and environment:
vba: vbCrLf (carriage return and line feed)
vba: Chr(13) & Chr(10) - and must be in that order. Chr() is the character function
sheet cell - I believe the only character that you have to worry about is chr(10) if you used Ctl+Enter to split your lines

So use the TTC dialog, choose "other" as the delimiter, click in the character box and press ctrl+Enter and it should work. I only mentioned the other aspects in case anyone reading this can use the other info.

 

[alansidman]

Assume your data is in Column A and your range has been configured as Table 1

Here is a Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByDelimiter("#(cr)#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Need to find what the character(s) between the lines is/are. Try something like this with one of the multi-line cells. Pull the formulas shown down until you get all the characters listed down a column & their codes beside. We need to find out how many "blank" cells appear between each section in the left column and what the corresponding codes for those blank cells are in the right column.

24 01 22.xlsm
	B	C
2	abc def ghi
3
4	a	97
5	b	98
6	c	99
7		10
8	d	100
9	e	101
10	f	102
11		10
12	g	103
13	h	104
14	i	105
Sample

Cell Formulas
Range	Formula
B4:B14	B4	=MID(B$2,ROWS(B$4:B4),1)
C4:C14	C4	=CODE(B4)

 

[Micron]

I mistakenly wrote Ctrl+Enter; should have been Alt+Enter. Oddly enough, if you entered the data using Alt+Enter to split the lines but specify Ctrl+Enter as the character to split on, it works as I suggested (and should work for any version)?

BEFORE

PREVIEW

 

[laundon]

Thanks for all your replies. Greatly appreciated.

I can't get any of the above to work on my data, however, a colleague has assisted and wrote a Python script for me so I'm all sorted.

Once again, many thanks, and @Peter_SSs, I've updated my account details

 

[Peter_SSs]

so I'm all sorted.

Great! Thanks for letting us know.

.. and for updating your details.