Ctrl + J in text to column

[lezawang]

Hi
I was reading the article below. User use Ctrl + J as a delimiter. I have found that using Ctrl + J or not I will get the same result. Excel break the text into columns without putting anything under [] other

Thank you.

 

[pgc01]

Hi

What do you mean?

Give an example with
- a small text
- what delimiters you use in the text-to-columns
- what part of the text goes into which column after text-to-columns executes

 

[lezawang]

Sorry I forgot to put the link, here it is

http://www.howtoexcel.org/tips-and-tricks/how-to-separate-data-in-a-cell-based-on-line-breaks/

Thanks

 

[thisoldman]

You can try Ctrl+m instead of Ctrl+j. Ctrl+m is the Apple line break character.

 

[pgc01]

Hi

What do you mean?

Give an example with
- a small text
- what delimiters you use in the text-to-columns
- what part of the text goes into which column after text-to-columns executes

Hi lezawang

I don't mean a link. This is a known technique.

I mean do yourself a test.


1 - Exit excel (not just closing your workbook).

2 - Start excel and in a new workbook enter some text and use Data-to-columns.

Do you see that the text is not split, across columns, one line in each column, as it would be if you used CTRL-J as a delimiter?

3 - Exit excel and start excel again and try it on more time. See that it does not happen.

 

[lezawang]

Thanks for your reply. I actually tried it both without using crt-j and with ctr-j and I do not see the difference. Here is my file after text-to-column without using any delimiters. I had name, address, city, country in one cell (enter+alt)

https://drive.google.com/open?id=0B5qI7A3TfxK5RU54dC1uUW95SnM

 

[pgc01]

Hi

Sorry, I tried with your file and it worked as I expected (with office 2010 at work and office 365 home)

It will only split the text into lines, written in the cells to the right if I use ctrl-j.

 

[lezawang]

Yes it is working. But that is not the issue. What I wanted to say and what I just found that. I opened new excel file and typed 4 lines in one cell using Enter+Alt, then I used Text-to-Columns feature. It did not working until I checked " Others [ ]" and typed Ctrl+J then it did work.
But after that I did not have to enter Ctrl+J anymore even if I opened a new file. It seems excel saved CTRL+J and I do not need to enter it anymore. I just need to click on "Other []" and thats it. So my question, does excel save Ctrl+J?

 

[thisoldman]

Excel remembers the last used text-to-data wizard settings. Gave me a headache with macros to open text files until I learned to clear the settings.

Bottom line, don't depend on the Ctrl+j sticking as a permanent setting.

 

[lezawang]

Thank you. How to clear the settings?