You have serial numbers like AA10D, AA11D and want to fill to AA12D, AA13D, and so on. The Excel Fill Handle won't do this. Learn how to solve it with a formula.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1801: Fill AA10B to AA11B.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, send by Mohammed Desouki.
He has a serial numbers that look like FF 10 D FF11 D, those aren't hex numbers apparently they are serial numbers.
I want to be able to drag down and generate FF12 D and FF13 D and so that's this question.
Now, this is not going to work so FF10 D selected both of those grab the fill handle drag and it just repeats the cells, it doesn't increase and what we know about Excel is you have any word like room 13 or table 14 and you grab the fill handle drag it will extend the numeric portion of that keeping the original word.
It also works if you have a number and then any word so 101 any word will extend a 102 103 and 104.
But, when the numbers in the middle so there's a word a number and a word this feature just does not work it does not increment.
So, what I suggested to Mohammed and he didn't understand and asked for this video is to break it down using the =LEFT (to get the word) &MID (to get the number) +1&MID (to get the last word).
So, let's do this =LEFT (A3, 2) &MID (A3, 3,2) +1&RIGHT (A3, 1) will work there and now when we drag this down you see that it increments like we would have expected it to increment had it been FF 11 or 11 FF.
So, that we're building something into Excel that is not existing here by using the formula.
However, this one is still going to fail when we get to FF 99D because it will temporarily go up to 100 d but then the formula is looking at just those two digits the 10 resets back to 11D.
So, a better way to go here when we hard code that we're getting two digits two digits what we should do instead is we should ask for the LEN of A3-3.
So, you see that right now.
There are five characters up here the first two characters are the prefix the last character is the suffix and the numbers everything in between by using the minus 3 we're taking out the first two characters and the last character and that should work perfectly even if we throw something bizarre like XY 1 2 3 4 5 6 Z it will be able to extend the number no matter how long the number is well up to I guess 15 digits then it will extend it properly.
Well, hey, 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 1801: Fill AA10B to AA11B.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, send by Mohammed Desouki.
He has a serial numbers that look like FF 10 D FF11 D, those aren't hex numbers apparently they are serial numbers.
I want to be able to drag down and generate FF12 D and FF13 D and so that's this question.
Now, this is not going to work so FF10 D selected both of those grab the fill handle drag and it just repeats the cells, it doesn't increase and what we know about Excel is you have any word like room 13 or table 14 and you grab the fill handle drag it will extend the numeric portion of that keeping the original word.
It also works if you have a number and then any word so 101 any word will extend a 102 103 and 104.
But, when the numbers in the middle so there's a word a number and a word this feature just does not work it does not increment.
So, what I suggested to Mohammed and he didn't understand and asked for this video is to break it down using the =LEFT (to get the word) &MID (to get the number) +1&MID (to get the last word).
So, let's do this =LEFT (A3, 2) &MID (A3, 3,2) +1&RIGHT (A3, 1) will work there and now when we drag this down you see that it increments like we would have expected it to increment had it been FF 11 or 11 FF.
So, that we're building something into Excel that is not existing here by using the formula.
However, this one is still going to fail when we get to FF 99D because it will temporarily go up to 100 d but then the formula is looking at just those two digits the 10 resets back to 11D.
So, a better way to go here when we hard code that we're getting two digits two digits what we should do instead is we should ask for the LEN of A3-3.
So, you see that right now.
There are five characters up here the first two characters are the prefix the last character is the suffix and the numbers everything in between by using the minus 3 we're taking out the first two characters and the last character and that should work perfectly even if we throw something bizarre like XY 1 2 3 4 5 6 Z it will be able to extend the number no matter how long the number is well up to I guess 15 digits then it will extend it properly.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
