Bill has decided that Mike is going to do all of the work today. In 'Almost Live' Episode #1527, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen work out very interesting solutions for separating numbers and text to extract values...I think...? Follow along with Mike and Bill as they produce solutions to whatever is really happening in this Episode. Enjoy!
Dueling Excel Podcast #101...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #101...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back. Some people call this the dueling Excel podcast.
Although, I'm not sure this one's much of a duel, because I'm just going to sit back and watch Mike do all the work.
Because he came up with a tough one.
I'm going to explain the problem.
I'm Bill Jelen from MrExcel. We'll be joined by Mike Gervin from Excel Is Fun.
Vijay sent this question in. I have some text over here and using the LEFT function to get the number. How cool!
and then try to use the aggregate function.
So this 9, says we're going to SUM and the 6, says we're going to ignore errors. But that is not working.
And then tried to use the array version of aggregate and that's clever, that's awesome.
Aggregate does work with arrays, if you're using 12 through 18 or 19.
It's only the new ones, that they added that the arrays work. They didn't make the arrays work for 1 through 11 which is just the one that they needed. The SUM.
Yeah right yeah what's up with that?
So Mike says that he has a formula that'll solve this. He actually says he has several formulas and it irritates me because I can't think of a single formula.
So I'm going to sit back there and watch him do these.
And I'm going to try and come up with one at the end.
So Mike! see what you have.
All right, thanks MrExcel.
You know a cheap and dirty one, the LEFT over there was a great idea.
So I'm just going to say IF this is equal to blank, which means when it gets down here, the LEFT function has trouble with that.
I'm going to say blank. Otherwise, please give me the LEFT of this , 1. It extracts that.
All right, and then copy this down.
Now wait a second. Now if I try to add these right here.
Alt = the SUM function is programmed to ignore text.
So what I really need to do is slightly alter this.
I'm going to do something like add 0 or do a double negative SUM operation on the number stored as text will convert it back to a number.
All right, so that's one way to do it, especially if you like it in a column and you want to see all the individual numbers here.
Now another cheap and dirty way.
Watch this, is let's just do LEFT.
And actually I should just go = LEFT of this , 1. Now LEFT when you copy it all the way down, if there's nothing it'll it'll put nothing there and then I'm just going to do the cheap and dirty way.
Since SUM function ignores text, if you use the operator plus, it won't ignore text.
That worked.
You wait, you teach Excel and you're going to use that formula?
Yeah but...
I'd fail any of your students who use that formula.
That would work.
Probably, stop 'em that way.
Yea it does work.
but that's bad. Okay.
And maybe if you wanted a single cell formula.
Let's see, sometimes doing an array formula, you can steal the single one and then just expand it and yeah.
If ah now that's not going to work.
How about the =IF ?
Oh no =LEFT and then I need to have an IF inside of it cuz I don't want the blanks.
So I'm going to say if anything in here is equal to blank, then give me a 0. Otherwise, give me this.
Now that's the IF dumping something into LEFT then I'm going to do 1 on that and just see what this will give me.
F9 Still it's giving me text, but it's all numbers, right.
So now I can do double negative and guess what? what if I put it in SUMPRODUCT. SUMPRODUCT is not going to work and the reason why is the IF function. We put an array in there and it's going to absolutely no matter what require Ctrl + Shift + Enter So let's not put PRODUCT, because some people might look at and oh it doesn't need Ctrl + Shift + Enter Ctrl + Shift + Enter and there's an 18.
Ctrl + Shift + Enter. CSE functions.
CSE the famous abbreviation from the MrExcel.
Oh shut that.
You know I started calling CSE because I could never remember what the heck that keystroke was and I would write it on a little sticky note, a little sticky note, but it's funny.
I would only use these about 6 times a year and the sticky notes, the glue on sticky notes, it would fall off and the cleaning crew would come through and my sticky note will be gone.
What was those key strokes so i started to call them CSE.
All right so the goal, as I understand it now I've been sitting back here is, we want to add up all these numbers. Is that right?
Yeah, yeah all right. Here we go, Ready? Alt + D +E Alt + D + E Fixed Width Click Next.
Get rid of that one right there.
We have that one and this one.
In the next step we keep the first one.
Second one. Do not import.
Finish.
Ctrl + 1 and we're going to say Custom and it is going to be. For positive numbers we're going to have 0 X school.
For negative numbers we're going to do nothing.
For 0 we're going to do nothing.
Click OK, all right.
We will send those back to Vijay.
Wow amazing.
We're not going to tell Vijay we changed anything.
We'll say, Vijay what's wrong? It's a simple SUM function there.
And Sweet Point to MrExcel.
I don't think so.
Ctrl + Shift + ~ Ctrl + Shift + what?
Ctrl + Shift + ~ What the heck is that?
What is Ctrl + Shift + ~ It applies to general number format.
Ctrl + Shift + ~ For any any number format it's an eraser.
I tell my students in my class that's the eraser for number formatting .
The eraser. That's, I am..
All right.
If anyone out there understands what Ctrl + Shift + ~ does? Please send me a note at bill@MrExcel and explain it to me.
You have to understand, so you know I flew out to Seattle last night.
I got there like at 2:00 in the morning, stuck for 3 hours.
Listen to a great stuff at Microsoft all day today.
But I I should not be doing dueling podcast today.
All right.
What do you mean? We're having so much fun.
We are but I'm coming up with stupid solutions but that's the beautiful thing about Excel.
There's more than one way to skin a cat.
Or a dog. Or a dog.
Well hey, I want to thank you for stopping by.
See you next time for another dueling Excel podcast from MrExcel. Excel Is Fun.
Although, I'm not sure this one's much of a duel, because I'm just going to sit back and watch Mike do all the work.
Because he came up with a tough one.
I'm going to explain the problem.
I'm Bill Jelen from MrExcel. We'll be joined by Mike Gervin from Excel Is Fun.
Vijay sent this question in. I have some text over here and using the LEFT function to get the number. How cool!
and then try to use the aggregate function.
So this 9, says we're going to SUM and the 6, says we're going to ignore errors. But that is not working.
And then tried to use the array version of aggregate and that's clever, that's awesome.
Aggregate does work with arrays, if you're using 12 through 18 or 19.
It's only the new ones, that they added that the arrays work. They didn't make the arrays work for 1 through 11 which is just the one that they needed. The SUM.
Yeah right yeah what's up with that?
So Mike says that he has a formula that'll solve this. He actually says he has several formulas and it irritates me because I can't think of a single formula.
So I'm going to sit back there and watch him do these.
And I'm going to try and come up with one at the end.
So Mike! see what you have.
All right, thanks MrExcel.
You know a cheap and dirty one, the LEFT over there was a great idea.
So I'm just going to say IF this is equal to blank, which means when it gets down here, the LEFT function has trouble with that.
I'm going to say blank. Otherwise, please give me the LEFT of this , 1. It extracts that.
All right, and then copy this down.
Now wait a second. Now if I try to add these right here.
Alt = the SUM function is programmed to ignore text.
So what I really need to do is slightly alter this.
I'm going to do something like add 0 or do a double negative SUM operation on the number stored as text will convert it back to a number.
All right, so that's one way to do it, especially if you like it in a column and you want to see all the individual numbers here.
Now another cheap and dirty way.
Watch this, is let's just do LEFT.
And actually I should just go = LEFT of this , 1. Now LEFT when you copy it all the way down, if there's nothing it'll it'll put nothing there and then I'm just going to do the cheap and dirty way.
Since SUM function ignores text, if you use the operator plus, it won't ignore text.
That worked.
You wait, you teach Excel and you're going to use that formula?
Yeah but...
I'd fail any of your students who use that formula.
That would work.
Probably, stop 'em that way.
Yea it does work.
but that's bad. Okay.
And maybe if you wanted a single cell formula.
Let's see, sometimes doing an array formula, you can steal the single one and then just expand it and yeah.
If ah now that's not going to work.
How about the =IF ?
Oh no =LEFT and then I need to have an IF inside of it cuz I don't want the blanks.
So I'm going to say if anything in here is equal to blank, then give me a 0. Otherwise, give me this.
Now that's the IF dumping something into LEFT then I'm going to do 1 on that and just see what this will give me.
F9 Still it's giving me text, but it's all numbers, right.
So now I can do double negative and guess what? what if I put it in SUMPRODUCT. SUMPRODUCT is not going to work and the reason why is the IF function. We put an array in there and it's going to absolutely no matter what require Ctrl + Shift + Enter So let's not put PRODUCT, because some people might look at and oh it doesn't need Ctrl + Shift + Enter Ctrl + Shift + Enter and there's an 18.
Ctrl + Shift + Enter. CSE functions.
CSE the famous abbreviation from the MrExcel.
Oh shut that.
You know I started calling CSE because I could never remember what the heck that keystroke was and I would write it on a little sticky note, a little sticky note, but it's funny.
I would only use these about 6 times a year and the sticky notes, the glue on sticky notes, it would fall off and the cleaning crew would come through and my sticky note will be gone.
What was those key strokes so i started to call them CSE.
All right so the goal, as I understand it now I've been sitting back here is, we want to add up all these numbers. Is that right?
Yeah, yeah all right. Here we go, Ready? Alt + D +E Alt + D + E Fixed Width Click Next.
Get rid of that one right there.
We have that one and this one.
In the next step we keep the first one.
Second one. Do not import.
Finish.
Ctrl + 1 and we're going to say Custom and it is going to be. For positive numbers we're going to have 0 X school.
For negative numbers we're going to do nothing.
For 0 we're going to do nothing.
Click OK, all right.
We will send those back to Vijay.
Wow amazing.
We're not going to tell Vijay we changed anything.
We'll say, Vijay what's wrong? It's a simple SUM function there.
And Sweet Point to MrExcel.
I don't think so.
Ctrl + Shift + ~ Ctrl + Shift + what?
Ctrl + Shift + ~ What the heck is that?
What is Ctrl + Shift + ~ It applies to general number format.
Ctrl + Shift + ~ For any any number format it's an eraser.
I tell my students in my class that's the eraser for number formatting .
The eraser. That's, I am..
All right.
If anyone out there understands what Ctrl + Shift + ~ does? Please send me a note at bill@MrExcel and explain it to me.
You have to understand, so you know I flew out to Seattle last night.
I got there like at 2:00 in the morning, stuck for 3 hours.
Listen to a great stuff at Microsoft all day today.
But I I should not be doing dueling podcast today.
All right.
What do you mean? We're having so much fun.
We are but I'm coming up with stupid solutions but that's the beautiful thing about Excel.
There's more than one way to skin a cat.
Or a dog. Or a dog.
Well hey, I want to thank you for stopping by.
See you next time for another dueling Excel podcast from MrExcel. Excel Is Fun.
