Today's Duel comes from a YouTube viewer who - depending on the original configuration of digits - would like to add "0"s and " - "s based on the first digit of the Number that has been recorded. Follow along with Episode #1554 as Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen offer their means to the solution.
Dueling Excel Podcast #106...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! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #106...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! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"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! It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We’ll be joined by Mike Girvin, from Excel Is Fun.
This is our episode 107: Add Zero and Dash.
All right, question sent in from YouTube.
If the first digit-- we have a series of numbers here.
If the first digit is a 3 insert a 0 at the front and a dash after the 4th digit.
If it's not a 3, then 0 at the front and a dash after the 3rd digit.
All right, so let's-- you know, my initial reaction is, “Hey, I wonder if we can do this with a custom number format”.
Choose those numbers, control one.
Choose Custom, and this is generally a great way that we can put leading zeros in, so; 0000-0000000.
And that will work perfectly from the numbers that start with a 3, but unfortunately there’re numbers that start with a four regarding two leading zeros instead of one; so we're just going to undo that.
Ctrl-z and go back.
All right, so I think we're going to have to start with an IF statement here; although both conditions, whether it starts with a 3 or not, have a zero at the front now.
So that comes outside of the IF statement.
=”0”&IF(LEFT(B5,1)=”3”,LEFT(B5,3)&”-“&Mid(B5,4,100).
Okay, so that handles this situation where we have 3 as the starting digit.
All other situations, though, we need to say, you know, what happens if it's not true.
So I'm coming up here into the formula bar, and I'm choosing all of the characters including that comma -- ,LEFT(B5,3)&”-“&Mid(B5,4,100) – Ctrl+c to copy, Ctrl+v to paste.
And now I've pasted the else portion of the IF statement.
So in this case, I want the two left digits and I want to start at position 3 instead of position 4: =”0”&IF(LEFT(B5,1)=”3”,LEFT(B5,3)&”-“&Mid(B5,4,100),LEFT(B5,2)&”-“&Mid(B5,3,100)) And let's see, there we go: 321.
We'll double-click to shoot that down, and we get a leading 0 and the dash after the first two digits.
So to me this looks like some sort of a phone number system, I'm not sure exactly what it is, but I think that's the way to solve it.
Mike, let's see what you have.
Mike Girvin - Thanks MrExcel!
Hey, I love that formula.
You used the Mid and my first idea was basically do this same form you did; except for, I took the LEFT and then joined it to the &, and I did RIGHT.
So, I took the RIGHT function to say, “Count all of them with the LEN and then subtract 3”.
So that way it would get all those numbers, right.
So there was two possibilities here we subtract the 3 and 2.
But then I was thinking, you know that custom number formatting, that would be another way to go.
So I could say the TEXT function, which allows us to take a value and do Custom number formatting.
So I'm just going to take MrExcel’s idea and in double quotes put “00000000000”, right.
That's the number format he did in cells, but here now we can get the TEXT function to do it in a formula.
Double- click and send that down.
But of course, it has that problem; and I actually added a few extra numbers just to test if I could come up with a formula that might do this right.
Too many zero.
So-- no problem, how about we vary the number format-- number of zeros as we copy this down.
We'll just say repeat.
REPT function can take the 0 in double quotes; and how many times do you want to repeat it.
Well, Len will give us the length; now that will give us one too few, because we want one leading, so we just add 1 right.
So, that gives me-- and then I need one more close parentheses.
=TEXT(A5,REPT(“0”,LEN(A5)+1).
Ctrl+Enter, double-click and send it down.
So that adds a leading zero only to one more than the length over here.
Now, the last little trick here is, we need a dash.
If there’s a 3, we need to go one-- so it needs to be the 4th one.
Zero, one, two, three and then there needs to be a dash.
Now here's the thing, there is a function that can insert a character: it's called the REPLACE function, and here's how REPLACE works.
We'll say take this and insert a character, but the replace is going to ask us where do you want to start?
One, two, three, four; well that looks like we want to start at four.
But if you're inserting, you go to five and then it will ask you how many characters.
You tell it zero and it will push the 9 over and put a character there.
So, right here, I'm going to say REPLACE; there's the old_text.
Comma and the starting number.
Now, the trick here is-- remember if it's a 3, we need a 5; but if it's a 4, we need a full, full-- sorry.
If it's anything besides a 3, we need to only go in four characters, right; one, two, three.
But we jump to the next one, to then force it to scoot over; so we need an IF statement here: IF I'm going to do the LEFT of this; comma, 1, because that's what we're looking for, equals 3.
Now, LEFT function spits out text even if it's a number, so this wouldn't work; that's a mismatch, number against text.
So of course I have to put that in double quotes.
IF it's 3 then what do I want: I need a 5 here.
Remember this is telling the REPLACE where to go to insert, so 5; otherwise, 4.
Close parenthesis.
So that IF is delivering a 4 or 5 and again the start number if you're inserting has to be one more than the position you think you're supposed to go to.
Comma; second important thing about inserting is you tell the number of characters, 0.
So the number of characters is 0; and then the new text, and double quote and dash.
Close parentheses, Ctrl+Enter, double-click and send it down.
=REPLACE(TEXT(A5,REPT(“0”,LEN(A5)+1)),IF(LEFT(A5,1)=”3”,5,4),0,”-“) I think that is working, so on the 3s it goes and puts it there, four; otherwise it puts it three.
Alright, throw it back to MrExcel.
Bill Jelen - Mike, that is so cool.
I can't believe it, I've never used that version of REPLACE.
So, the trick is if I want to insert something before the C in “cool”, I have to count the character position of the C. That's using =LEN of this is 25.
So we'll use =REPLACE(A2,25,0,A7).
Alright, so if we have nothing at A7, it's just the string; if we choose something—oh, that is amazing.
A great way to insert new text at a certain spot.
That is cool.
Point to Excel Is Fun.
Hey, I wanna thank everyone for stopping by.
I'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel.
We’ll be joined by Mike Girvin, from Excel Is Fun.
This is our episode 107: Add Zero and Dash.
All right, question sent in from YouTube.
If the first digit-- we have a series of numbers here.
If the first digit is a 3 insert a 0 at the front and a dash after the 4th digit.
If it's not a 3, then 0 at the front and a dash after the 3rd digit.
All right, so let's-- you know, my initial reaction is, “Hey, I wonder if we can do this with a custom number format”.
Choose those numbers, control one.
Choose Custom, and this is generally a great way that we can put leading zeros in, so; 0000-0000000.
And that will work perfectly from the numbers that start with a 3, but unfortunately there’re numbers that start with a four regarding two leading zeros instead of one; so we're just going to undo that.
Ctrl-z and go back.
All right, so I think we're going to have to start with an IF statement here; although both conditions, whether it starts with a 3 or not, have a zero at the front now.
So that comes outside of the IF statement.
=”0”&IF(LEFT(B5,1)=”3”,LEFT(B5,3)&”-“&Mid(B5,4,100).
Okay, so that handles this situation where we have 3 as the starting digit.
All other situations, though, we need to say, you know, what happens if it's not true.
So I'm coming up here into the formula bar, and I'm choosing all of the characters including that comma -- ,LEFT(B5,3)&”-“&Mid(B5,4,100) – Ctrl+c to copy, Ctrl+v to paste.
And now I've pasted the else portion of the IF statement.
So in this case, I want the two left digits and I want to start at position 3 instead of position 4: =”0”&IF(LEFT(B5,1)=”3”,LEFT(B5,3)&”-“&Mid(B5,4,100),LEFT(B5,2)&”-“&Mid(B5,3,100)) And let's see, there we go: 321.
We'll double-click to shoot that down, and we get a leading 0 and the dash after the first two digits.
So to me this looks like some sort of a phone number system, I'm not sure exactly what it is, but I think that's the way to solve it.
Mike, let's see what you have.
Mike Girvin - Thanks MrExcel!
Hey, I love that formula.
You used the Mid and my first idea was basically do this same form you did; except for, I took the LEFT and then joined it to the &, and I did RIGHT.
So, I took the RIGHT function to say, “Count all of them with the LEN and then subtract 3”.
So that way it would get all those numbers, right.
So there was two possibilities here we subtract the 3 and 2.
But then I was thinking, you know that custom number formatting, that would be another way to go.
So I could say the TEXT function, which allows us to take a value and do Custom number formatting.
So I'm just going to take MrExcel’s idea and in double quotes put “00000000000”, right.
That's the number format he did in cells, but here now we can get the TEXT function to do it in a formula.
Double- click and send that down.
But of course, it has that problem; and I actually added a few extra numbers just to test if I could come up with a formula that might do this right.
Too many zero.
So-- no problem, how about we vary the number format-- number of zeros as we copy this down.
We'll just say repeat.
REPT function can take the 0 in double quotes; and how many times do you want to repeat it.
Well, Len will give us the length; now that will give us one too few, because we want one leading, so we just add 1 right.
So, that gives me-- and then I need one more close parentheses.
=TEXT(A5,REPT(“0”,LEN(A5)+1).
Ctrl+Enter, double-click and send it down.
So that adds a leading zero only to one more than the length over here.
Now, the last little trick here is, we need a dash.
If there’s a 3, we need to go one-- so it needs to be the 4th one.
Zero, one, two, three and then there needs to be a dash.
Now here's the thing, there is a function that can insert a character: it's called the REPLACE function, and here's how REPLACE works.
We'll say take this and insert a character, but the replace is going to ask us where do you want to start?
One, two, three, four; well that looks like we want to start at four.
But if you're inserting, you go to five and then it will ask you how many characters.
You tell it zero and it will push the 9 over and put a character there.
So, right here, I'm going to say REPLACE; there's the old_text.
Comma and the starting number.
Now, the trick here is-- remember if it's a 3, we need a 5; but if it's a 4, we need a full, full-- sorry.
If it's anything besides a 3, we need to only go in four characters, right; one, two, three.
But we jump to the next one, to then force it to scoot over; so we need an IF statement here: IF I'm going to do the LEFT of this; comma, 1, because that's what we're looking for, equals 3.
Now, LEFT function spits out text even if it's a number, so this wouldn't work; that's a mismatch, number against text.
So of course I have to put that in double quotes.
IF it's 3 then what do I want: I need a 5 here.
Remember this is telling the REPLACE where to go to insert, so 5; otherwise, 4.
Close parenthesis.
So that IF is delivering a 4 or 5 and again the start number if you're inserting has to be one more than the position you think you're supposed to go to.
Comma; second important thing about inserting is you tell the number of characters, 0.
So the number of characters is 0; and then the new text, and double quote and dash.
Close parentheses, Ctrl+Enter, double-click and send it down.
=REPLACE(TEXT(A5,REPT(“0”,LEN(A5)+1)),IF(LEFT(A5,1)=”3”,5,4),0,”-“) I think that is working, so on the 3s it goes and puts it there, four; otherwise it puts it three.
Alright, throw it back to MrExcel.
Bill Jelen - Mike, that is so cool.
I can't believe it, I've never used that version of REPLACE.
So, the trick is if I want to insert something before the C in “cool”, I have to count the character position of the C. That's using =LEN of this is 25.
So we'll use =REPLACE(A2,25,0,A7).
Alright, so if we have nothing at A7, it's just the string; if we choose something—oh, that is amazing.
A great way to insert new text at a certain spot.
That is cool.
Point to Excel Is Fun.
Hey, I wanna thank everyone for stopping by.
I'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
