Don from Europe calls with today's question: How can I add some text next to a number and still keep the number "as a number" that will flow into future formulas. Episode 991 shows you a cool trick for achieving this effect.
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 we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey... All right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Got a great question today before we get to that, I just wanna point out you know Tax Day coming up April 15th.
How the amazing thing especially when you do seminars for people who work in accounting.
The day after Tax Day begins seminar season.
So, I have a couple of upcoming seminars, April 16th in the afternoon, Springfield, Illinois.
April 17th Louisville, Kentucky and then the next week April 22nd Springfield, Missouri, and then up to a place where I've never been before Sioux Falls, South Dakota on Friday April 24th.
If you're in any of those places, love to have you stop by and check out the seminar and you know, make sure to mention that you watch the podcast and then the next week.
The big event the 3 day boot camp, Power Analyst Boot Camp with Mike Alexander.
Mike is the co-author with a Pivot Table Data Crunching book, like and I do a 3 day seminar, that'll be April 28th, 29th and 30th in Chicago. Going back to Chicago.
So, if you're interested any of those, would love to have you stopped by.
All right now, today's question.
This is a call from a former co-worker of mine Don.
Don's now in Europe and called, he said hey, I have some numbers here and the thing that's driving me crazy, is a little formula is adding up those numbers, and I need to make a note next to the numbers.
Don says, I don't like cell comments.
The cell comment isn't you know you have to do stuff to get it to appear and it doesn't appear in the cell.
I just want to put a word to the left of the 3540 there may be, like approved or something like that but when I try and actually type the word and then a space and the number.
Well, that's not a number anymore.
So, my formula doesn't work.
Ok so, I have two solutions to this I ran both of them passed Don.
Don saying, I'd really like to just get over there and say you know.
How can I put a word in but not have it screw up the number?
I said well, there's a great trick.
Let's select that cell we're going to press control+1, to get to Format Cells and initially it set to General, but I want to click on Custom and get rid of the word General so choose those words press delete or the backspace or something like that and then whatever word you want to put, put that in quotes, double quotes.
So, double quote maybe the word Approved space the closing quote and then something to put a number format and they're like a zero would work or if you want to be more specific, pound comma pound pound 0, will work click OK and you see that.
Now, we have a couple of things first of all there's a number here 3540, the word Approved appears next to the number, but it is not part of the cell at all.
It could change the number something's like 9999, there you see that that now appears in the cell it does change the total.
So, it's still being treated like a number and I said to die said if you happen to have to put the same comment, next to several cells.
It's gonna be very easy to do because we can take that first cell, double click the Format Painter and then paint that word into other cells.
I thought this was a beautiful idea, but of course I could tell by Don's voice that he wasn't impressed.
So, this is one way to do it.
Tomorrow we'll take a look at another way to solve this problem.
I 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 we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey... All right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Got a great question today before we get to that, I just wanna point out you know Tax Day coming up April 15th.
How the amazing thing especially when you do seminars for people who work in accounting.
The day after Tax Day begins seminar season.
So, I have a couple of upcoming seminars, April 16th in the afternoon, Springfield, Illinois.
April 17th Louisville, Kentucky and then the next week April 22nd Springfield, Missouri, and then up to a place where I've never been before Sioux Falls, South Dakota on Friday April 24th.
If you're in any of those places, love to have you stop by and check out the seminar and you know, make sure to mention that you watch the podcast and then the next week.
The big event the 3 day boot camp, Power Analyst Boot Camp with Mike Alexander.
Mike is the co-author with a Pivot Table Data Crunching book, like and I do a 3 day seminar, that'll be April 28th, 29th and 30th in Chicago. Going back to Chicago.
So, if you're interested any of those, would love to have you stopped by.
All right now, today's question.
This is a call from a former co-worker of mine Don.
Don's now in Europe and called, he said hey, I have some numbers here and the thing that's driving me crazy, is a little formula is adding up those numbers, and I need to make a note next to the numbers.
Don says, I don't like cell comments.
The cell comment isn't you know you have to do stuff to get it to appear and it doesn't appear in the cell.
I just want to put a word to the left of the 3540 there may be, like approved or something like that but when I try and actually type the word and then a space and the number.
Well, that's not a number anymore.
So, my formula doesn't work.
Ok so, I have two solutions to this I ran both of them passed Don.
Don saying, I'd really like to just get over there and say you know.
How can I put a word in but not have it screw up the number?
I said well, there's a great trick.
Let's select that cell we're going to press control+1, to get to Format Cells and initially it set to General, but I want to click on Custom and get rid of the word General so choose those words press delete or the backspace or something like that and then whatever word you want to put, put that in quotes, double quotes.
So, double quote maybe the word Approved space the closing quote and then something to put a number format and they're like a zero would work or if you want to be more specific, pound comma pound pound 0, will work click OK and you see that.
Now, we have a couple of things first of all there's a number here 3540, the word Approved appears next to the number, but it is not part of the cell at all.
It could change the number something's like 9999, there you see that that now appears in the cell it does change the total.
So, it's still being treated like a number and I said to die said if you happen to have to put the same comment, next to several cells.
It's gonna be very easy to do because we can take that first cell, double click the Format Painter and then paint that word into other cells.
I thought this was a beautiful idea, but of course I could tell by Don's voice that he wasn't impressed.
So, this is one way to do it.
Tomorrow we'll take a look at another way to solve this problem.
I 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.
