Ctrl + J in text to column

[scottwiebe]

I already read the other thread with the same name, but this still isn't working for me.

I have a column of data where each cell has 2 lines. I want to split these lines into the next column using the the text-to-columns feature. When I set a delimiter of other and enter ctrl+J, the preview doesn't change at all and when I follow through with it, the data has not been split up.

I thought maybe it was my formatting so to test, I made a new column and put the numbers 1, 2, 3, 4 all in separate lines using alt+enter. When I went to perform the text-to-columns it worked exactly how it should. I compared my previous data to this test data and couldn't find any difference in formatting. I even copied my test cell and pasted formatting on the original data, but to no avail.

What am I missing?

 

[Rick Rothstein]

Assuming one of your cells that does not work is A1 (change as needed), execute this code in the VBA Immediate Window...

? InStr(Range("A1").Value, vbLf)

and tell me what prints out.

 

[scottwiebe]

Assuming one of your cells that does not work is A1 (change as needed), execute this code in the VBA Immediate Window...

? InStr(Range("A1").Value, vbLf)

and tell me what prints out.

I'm sorry, when it comes to VBA, I am a lost puppy.
In the attached image is what I've done and I'm not sure what else exactly you are asking me to do.

 

[MARK858]

Press Ctrl+G and the Immediate window should appear, copy and paste what Rick posted in the Immediate window and press Enter. You'll get the result underneath.

 

[sandy666]

edit:
Oops, Mark bit me

 

[scottwiebe]

Assuming one of your cells that does not work is A1 (change as needed), execute this code in the VBA Immediate Window...

? InStr(Range("A1").Value, vbLf)

and tell me what prints out.

Hi Rick, sorry for taking so long to get back to you. I just entered the code into the immediate window and this is what I get.