Alt+Enter to control word wrap
How to break apart data separated by Alt+Enter in Excel
How to break apart data separated by Alt+Enter in Excel
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2132: ALT+ENTER Is A Better Way To Control Word Wrap.
I used to have a manager who would create the longest headings I've ever seen, like this one right here, and he would go into FORMAT CELLS and ALIGNMENT and turn on WRAP TEXT or use the item up there in the HOME tab, so, HOME and WRAP TEXT, like that.
Of course that was long before they had that symbol.
That symbol came along in Excel 2007.
We used to have to go into the FORMAT CELLS dialog box, but he didn't want this to say PROFIT AND LOSS FOR.
He wanted it to say PROFIT AND LOSS.
So, he would try and sit here and play with the column widths to get the word FOR to move down to the next row, but then, you know, it was just a mess, alright?
So, if you want to take control of that, what you can do is type the first line -- PROFIT AND LOSS -- and then press ALT+ENTER and then, FOR THE TWELVE MONTHS, ALT ENTER, ENDING DECEMBER 31ST, ALT+ENTER, (UNAUDITED), ALT+ENTER, (UNREVIEWED), and you get to control where each line breaks like that.
You don't have to worry about trying to play with the column widths anymore.
I was at a client’s doing a seminar years ago, 10 years ago, and someone said, hey, can you come back and look at something on my computer, and while they were opening the file, I saw a great little sticky note.
I'll never forget the sticky note.
It had some text and then a whole bunch of underscores, like 14 underscores, and then some longer text and then even more underscores and then something really short like that, and they had figured out that if they would turn on WRAP TEXT based on this report that they were creating over and over and over again that, based on that width, that would have everything line up.
So, someone had taken the time to count how many space bars they had to press after each line and, you know, had drawn that out, alright, which is great.
I said, hey, I love that, that's awesome, but let me teach you this amazing trick here in Excel where we can just type some text, ALT+ENTER, some longer text, ALT+ETNER, and then something short, and it will wrap in the right space without having to type all the space bars.
Now, you know, sometimes someone gets a hold of this ALT+ENTER thing and they go kind of crazy entering all sorts of things in one cell.
Like, I've gotten mailing lists before where name, street and city, state, zip are all in one cell.
If you need to break this apart, this is awesome.
We're going to go to DATA, TEXT TO COLUMNS, say it's DELIMITED, but in step 2, the delimiter is going to be OTHER, and, right here, we're going to type CONTROL+J. CONTROL+J is apparently the shortcut for ALT+ENTER.
I have no idea why CONTROL+J and, you know, I went searching for it.
I found an old IBM computer manual from the 1980s and there was one page there that listed a series of control codes and CONTROL+J was the code for linefeed, but then I went and checked all the other codes on that page and they don't work in that dialogue block.
So, I have no idea how that one bit of code ended up working in the dialog box.
You know, ALT+ENTER is a character 10.
J is the 10th letter the alphabet.
Who knows what the real reason is.
Alright.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I used to have a manager who would create the longest headings I've ever seen, like this one right here, and he would go into FORMAT CELLS and ALIGNMENT and turn on WRAP TEXT or use the item up there in the HOME tab, so, HOME and WRAP TEXT, like that.
Of course that was long before they had that symbol.
That symbol came along in Excel 2007.
We used to have to go into the FORMAT CELLS dialog box, but he didn't want this to say PROFIT AND LOSS FOR.
He wanted it to say PROFIT AND LOSS.
So, he would try and sit here and play with the column widths to get the word FOR to move down to the next row, but then, you know, it was just a mess, alright?
So, if you want to take control of that, what you can do is type the first line -- PROFIT AND LOSS -- and then press ALT+ENTER and then, FOR THE TWELVE MONTHS, ALT ENTER, ENDING DECEMBER 31ST, ALT+ENTER, (UNAUDITED), ALT+ENTER, (UNREVIEWED), and you get to control where each line breaks like that.
You don't have to worry about trying to play with the column widths anymore.
I was at a client’s doing a seminar years ago, 10 years ago, and someone said, hey, can you come back and look at something on my computer, and while they were opening the file, I saw a great little sticky note.
I'll never forget the sticky note.
It had some text and then a whole bunch of underscores, like 14 underscores, and then some longer text and then even more underscores and then something really short like that, and they had figured out that if they would turn on WRAP TEXT based on this report that they were creating over and over and over again that, based on that width, that would have everything line up.
So, someone had taken the time to count how many space bars they had to press after each line and, you know, had drawn that out, alright, which is great.
I said, hey, I love that, that's awesome, but let me teach you this amazing trick here in Excel where we can just type some text, ALT+ENTER, some longer text, ALT+ETNER, and then something short, and it will wrap in the right space without having to type all the space bars.
Now, you know, sometimes someone gets a hold of this ALT+ENTER thing and they go kind of crazy entering all sorts of things in one cell.
Like, I've gotten mailing lists before where name, street and city, state, zip are all in one cell.
If you need to break this apart, this is awesome.
We're going to go to DATA, TEXT TO COLUMNS, say it's DELIMITED, but in step 2, the delimiter is going to be OTHER, and, right here, we're going to type CONTROL+J. CONTROL+J is apparently the shortcut for ALT+ENTER.
I have no idea why CONTROL+J and, you know, I went searching for it.
I found an old IBM computer manual from the 1980s and there was one page there that listed a series of control codes and CONTROL+J was the code for linefeed, but then I went and checked all the other codes on that page and they don't work in that dialogue block.
So, I have no idea how that one bit of code ended up working in the dialog box.
You know, ALT+ENTER is a character 10.
J is the 10th letter the alphabet.
Who knows what the real reason is.
Alright.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
