In Today's Podcast, Episode #1373, Ram is using =CLEAN to get rid of the hard-return in a cell, but when he does this, he is losing the space between the words in that cell. Bill shows us how to use the =SUBSTITUTE function instead to yield a better result.
...This blog is the video 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 blog is the video 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:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1373: CLEAN, but not too clean.
Well hey, welcome back to the MrExcel netcast.
Our good friend Ram sent this question in.
Ram is using the “Clean” function.
Look at this, someone came over here and they used the good old Alt+Enter trick.
Tom, Alt+Enter.
Smith Alt+Right and it creates that cell that's two lines.
Ram was using the Clean function which does a great job of getting rid of that hard return but it's not leaving a space between the words.
Ram wants that space there.
Okay, so rather than using Clean, which is a clever function I'm going to use the formula equal substitute.
Equal substitute says it’s looking for text.
That must be the text over here in A2 and then old_text; the old_text, how do we type a hard return here?
We use CHAR and that's a function.
10.
10 is what you get when you press Alt+Enter.
It’s the old, old, old ASCII code for a line feed; go figure, anyway.
We want to replace it with in quotes space there we go.
The Formula =SUBSTITUTE(A2,CHAR(10),””) So that gets us the equivalent of Clean but it replaces that hard return the CHAR 10; the character 10 with a space.
Hey I want to thank Ram for sending that question in.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1373: CLEAN, but not too clean.
Well hey, welcome back to the MrExcel netcast.
Our good friend Ram sent this question in.
Ram is using the “Clean” function.
Look at this, someone came over here and they used the good old Alt+Enter trick.
Tom, Alt+Enter.
Smith Alt+Right and it creates that cell that's two lines.
Ram was using the Clean function which does a great job of getting rid of that hard return but it's not leaving a space between the words.
Ram wants that space there.
Okay, so rather than using Clean, which is a clever function I'm going to use the formula equal substitute.
Equal substitute says it’s looking for text.
That must be the text over here in A2 and then old_text; the old_text, how do we type a hard return here?
We use CHAR and that's a function.
10.
10 is what you get when you press Alt+Enter.
It’s the old, old, old ASCII code for a line feed; go figure, anyway.
We want to replace it with in quotes space there we go.
The Formula =SUBSTITUTE(A2,CHAR(10),””) So that gets us the equivalent of Clean but it replaces that hard return the CHAR 10; the character 10 with a space.
Hey I want to thank Ram for sending that question in.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
