Someone used Alt+Enter to enter three fields in hundreds of cells. How can this be broken into separate columns. In Episode 959, I discuss MID, CODE, CHAR, and SUBSTITUTE functions to solve the problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data How are we gonna analyze this. Well, let's fire up a pivot table.
Let's see if you solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, here's a question that came out of my Chicago seminar last week.
Interesting question the person had a data set where someone had used word wrap to put name, address, city, state, zip, all in one cell and you know I've shown how to do this before not ever recommending this of course.
Type the name and then hold down Alt+Enter and then the address Alt+Enter and then the city And you basically get to control where the words wrap and the person needed to take this text and convert it back to multiple columns or something like that and you know in using Text to Columns, there's no good way in step 2 to say hey, we want that that carriage return to be the Delimiter.
So, here's, here's how we attack this problem.
I wasn't exactly sure what to do at first.
First thing, I want to do is I want to turn off wrap text, that lets me see what I'm dealing with here and you can see there is actually an unprintable character where we typed, Alt+Enter.
So, I want to find out what that character is and so I always attack it this way.
I say I want the, =MID(, of this cell press the F4 key, and then where do I want to start, I want to start at the row of A1, that's a great way of typing 1 for a length of 1 and wanna copy that down I'm going to get all the letters from the cell one at a time.
So, there's Bill Jelen, right there. That's the character that's the one that I want to isolate.
So now, this cell has just that one character and I can use a great function called CODE.
The CODE, the CODE tells me that that character is a character 10.
Now, sometimes you're going to get data from the web, and you're going to see other unprintable characters like character 160 is real popular now on the web and we need to convert that back to something else.
So now, that I know what character it is that I need to change I'm going to use a function called SUBSTITUTE.
=SUBSTITUTE(, and says okay where's the text, the text is here in A1 the old text.
Now, I need to type that character 10 their, of course pressing control+enter is not going to work.
So, what I'm going to do is use CHAR, another function and use the code number.
So, I wanna convert every occurrence of character 10 to let's say a semicolon and we'll close that and so now, you'll see what it does is it takes my text and instead of having the unprintable character 10, I have a semicolon.
All right, convert these formulas to values so that's to it this way right clicked right, drag right, drag left, let go Copy Here as Values Only you can use whatever method like to use.
And now, that we have that with the semicolon very easy to use Text to Columns, its Delimited the Delimiter is the Semicolon and click Finish and we've now separated that data out into three columns.
So, a little bit of a hassle, but certainly easier and going through and you know stripping things out again and again and again, again.
I suppose you could also do this with a formula using Find, So, figure out where that character 10 is appearing and then using left and mid and so on to break it out.
So if you have a bunch of them, text to columns is just as easy.
So, I wanna thanks the folks in Chicago for having me out great question that came up during that seminar.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data How are we gonna analyze this. Well, let's fire up a pivot table.
Let's see if you solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, here's a question that came out of my Chicago seminar last week.
Interesting question the person had a data set where someone had used word wrap to put name, address, city, state, zip, all in one cell and you know I've shown how to do this before not ever recommending this of course.
Type the name and then hold down Alt+Enter and then the address Alt+Enter and then the city And you basically get to control where the words wrap and the person needed to take this text and convert it back to multiple columns or something like that and you know in using Text to Columns, there's no good way in step 2 to say hey, we want that that carriage return to be the Delimiter.
So, here's, here's how we attack this problem.
I wasn't exactly sure what to do at first.
First thing, I want to do is I want to turn off wrap text, that lets me see what I'm dealing with here and you can see there is actually an unprintable character where we typed, Alt+Enter.
So, I want to find out what that character is and so I always attack it this way.
I say I want the, =MID(, of this cell press the F4 key, and then where do I want to start, I want to start at the row of A1, that's a great way of typing 1 for a length of 1 and wanna copy that down I'm going to get all the letters from the cell one at a time.
So, there's Bill Jelen, right there. That's the character that's the one that I want to isolate.
So now, this cell has just that one character and I can use a great function called CODE.
The CODE, the CODE tells me that that character is a character 10.
Now, sometimes you're going to get data from the web, and you're going to see other unprintable characters like character 160 is real popular now on the web and we need to convert that back to something else.
So now, that I know what character it is that I need to change I'm going to use a function called SUBSTITUTE.
=SUBSTITUTE(, and says okay where's the text, the text is here in A1 the old text.
Now, I need to type that character 10 their, of course pressing control+enter is not going to work.
So, what I'm going to do is use CHAR, another function and use the code number.
So, I wanna convert every occurrence of character 10 to let's say a semicolon and we'll close that and so now, you'll see what it does is it takes my text and instead of having the unprintable character 10, I have a semicolon.
All right, convert these formulas to values so that's to it this way right clicked right, drag right, drag left, let go Copy Here as Values Only you can use whatever method like to use.
And now, that we have that with the semicolon very easy to use Text to Columns, its Delimited the Delimiter is the Semicolon and click Finish and we've now separated that data out into three columns.
So, a little bit of a hassle, but certainly easier and going through and you know stripping things out again and again and again, again.
I suppose you could also do this with a formula using Find, So, figure out where that character 10 is appearing and then using left and mid and so on to break it out.
So if you have a bunch of them, text to columns is just as easy.
So, I wanna thanks the folks in Chicago for having me out great question that came up during that seminar.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
