Annabeth enters Credit Card Numbers, along with other information into an Excel Spreadsheet. The issue is that the last two numbers of the card number is lost. How do we get the full card number into a cell? Change the Cell Formatting so that the number is seen as Text and not a number. But then... there is more. And - of course - Bill and Mike have differing ideas about solving the issues. Follow along with Episode #1702 as Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen provide their solutions and allow you to choose which one works for you.
Dueling Excel Podcast #122...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] Slaying Excel Dragons
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! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #122...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] Slaying Excel Dragons
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! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey, welcome back to the Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by a Mike Girvin from ExcelIsFun, this is our episode 122 - Entering Credit Cards in Excel!
This is a really common problem, people try and keep track their credit cards in Excel.
Annabeth is trying to enter credit card numbers, and she's losing the last digit every single time here.
Let's try 4300222233334444, BAM, that last 4 goes away, it replaced by a 0.
Expiration date, so we have an expiration date of 02/17, and we lose the leading 0.
3-digit code 123 works great, but 023 losses the leading 0.
Alright, the problem here is that Excel only deals with 15 digits of precision.
Now this week on the MrExcel podcast on Wednesday, I had a number of 451 septillion, there were so many digits that we lost, you know, everything after the first 15 digits.
And generally that's close enough, but it's really frustrating with credit cards, because we need that last digit, that last digit is really important!
So what we're going to do, is choose these cells here, and actually I'm going to do all of these, and go to the Home tab, change from a Number format to a Text format.
Text format says "Hey, let me enter whatever I want, don't try and treat it as a number, don't try and use any of your Excel mojo on it, it's just text." Let me have it, 4300222233334444, and the last digit stays there.
If you had some other kind, a part number, you know, that had 24 numbers, oh yeah, it's still going to work, alright, so it's just text.
Now you can't do any math with it, but that's OK, and expiration date of a 01/16, it's going to stay exactly the way you enter, with the leading 0.
Over here with the 3-digit code, I'm not going to convert that to text, I'm just going to do Ctrl+1, and a Custom number format of 3 zeros, 1-2-3, like that.
And you see that then, we enter 023.
Now I need to copy that formatting down here, Custom, 1-2-3, I could have done Paste Special Format, but just as fast.
Alright, so let's try something else, 3700222233334444, it stays, and a 01/17 stays, and 047 stays.
There we go, a little bit of formatting will allow you to do those credit card numbers with all their 16-digit glory.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel!
Hey, Text number formatting, Custom number formatting, 000, that really is the way to go!
However, there's an amazing array formula that can convert- oh wait a second, no that's a joke, there's no array formula for this problem.
This is, as MrExcel says, 15 digits, that's all Excel is going to see, so we have to use Text.
Alright, so you used number formatting.
You know I suppose, I could come here, I'm going to hit the Del key to delete content, and then I'm going to come up here and apply General.
If you use a lead apostrophe, then it doesn't matter what the number format is, it is considered Text.
So lead ' and then 01/15, lead ' and then 005.
Alright, no array formula here, just lead apostrophe.
Throwback to MrExcel!
Bill: Mike, that's beautiful, this is a record, this is the first Dueling podcast in months, where you haven't kicked out an array formula, I haven't kicked out some VBA, just simple number formatting, the lead apostrophe, I love that.
I'm going to bore you, since we have so much time here, going back to the days of Lotus 1-2-3, there were actually three characters: a lead ' would say "To left-justify", a lead " would say "To right-justify", and a lead ^, Shift+6, that little hat there would say "To center the entry." So those were at Lotus 1-2-3 formatting commands, and so the lead ' still works to this day in Excel.
Well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
I'm Bill Jelen from MrExcel, I'll be joined by a Mike Girvin from ExcelIsFun, this is our episode 122 - Entering Credit Cards in Excel!
This is a really common problem, people try and keep track their credit cards in Excel.
Annabeth is trying to enter credit card numbers, and she's losing the last digit every single time here.
Let's try 4300222233334444, BAM, that last 4 goes away, it replaced by a 0.
Expiration date, so we have an expiration date of 02/17, and we lose the leading 0.
3-digit code 123 works great, but 023 losses the leading 0.
Alright, the problem here is that Excel only deals with 15 digits of precision.
Now this week on the MrExcel podcast on Wednesday, I had a number of 451 septillion, there were so many digits that we lost, you know, everything after the first 15 digits.
And generally that's close enough, but it's really frustrating with credit cards, because we need that last digit, that last digit is really important!
So what we're going to do, is choose these cells here, and actually I'm going to do all of these, and go to the Home tab, change from a Number format to a Text format.
Text format says "Hey, let me enter whatever I want, don't try and treat it as a number, don't try and use any of your Excel mojo on it, it's just text." Let me have it, 4300222233334444, and the last digit stays there.
If you had some other kind, a part number, you know, that had 24 numbers, oh yeah, it's still going to work, alright, so it's just text.
Now you can't do any math with it, but that's OK, and expiration date of a 01/16, it's going to stay exactly the way you enter, with the leading 0.
Over here with the 3-digit code, I'm not going to convert that to text, I'm just going to do Ctrl+1, and a Custom number format of 3 zeros, 1-2-3, like that.
And you see that then, we enter 023.
Now I need to copy that formatting down here, Custom, 1-2-3, I could have done Paste Special Format, but just as fast.
Alright, so let's try something else, 3700222233334444, it stays, and a 01/17 stays, and 047 stays.
There we go, a little bit of formatting will allow you to do those credit card numbers with all their 16-digit glory.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel!
Hey, Text number formatting, Custom number formatting, 000, that really is the way to go!
However, there's an amazing array formula that can convert- oh wait a second, no that's a joke, there's no array formula for this problem.
This is, as MrExcel says, 15 digits, that's all Excel is going to see, so we have to use Text.
Alright, so you used number formatting.
You know I suppose, I could come here, I'm going to hit the Del key to delete content, and then I'm going to come up here and apply General.
If you use a lead apostrophe, then it doesn't matter what the number format is, it is considered Text.
So lead ' and then 01/15, lead ' and then 005.
Alright, no array formula here, just lead apostrophe.
Throwback to MrExcel!
Bill: Mike, that's beautiful, this is a record, this is the first Dueling podcast in months, where you haven't kicked out an array formula, I haven't kicked out some VBA, just simple number formatting, the lead apostrophe, I love that.
I'm going to bore you, since we have so much time here, going back to the days of Lotus 1-2-3, there were actually three characters: a lead ' would say "To left-justify", a lead " would say "To right-justify", and a lead ^, Shift+6, that little hat there would say "To center the entry." So those were at Lotus 1-2-3 formatting commands, and so the lead ' still works to this day in Excel.
Well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
