Haig wants a formula that will increment a cell with 1-2 into 3-4 and then 5-6 and so on. While the formula ends up being fairly long, it is a collection of steps to break the original cell apart and increment.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1969 - Formula to Increment 1-2 to 3-4!
All right, today's question sent in from Haig, How to make an automatic range counter?
For example, if I start with the number 1-2, I want that to increment to 3-4, and then 5-6! Alright, well this is a complicated formula, but each piece of the formula, is really kind of simple, first thing we want to do is, you want to use the find command to look for that dash in A1.
Right, here, in these small numbers it's always in the second position, but if we get up to the point where it's like 101-102, well, then it's going to be in the fourth position.
So using the FIND is the first piece here.
And then where does the second number start?
Well that's one more than the find, so =FIND()+1 locates where the second number starts, and then what is the second number.
Alright so I use here the MID of A1, this answer, and then I just chose a really big number, like 20, or 10, you know who knows, is this is going to give to millions how far we're going to copy this?
20 is just insanely large, this is going to start at position 3, for a length of 20, which is just going to be, in this case, the digit 2.
And that isolates for me the second number.
And then add one to that, alright, so wherever this cell ended, the next cell is going to start, at one more than that, so that's just a simple +1, right there in my formula.
Alright and then finally, I concatenate a dash, so that's &"-" . At this point we have the first part of the next cell, but then what we have to do, is figure out the second digit.
This is really going to be incredibly similar, we're going to have to go back, figure out where the dash is, figure out where the second number starts, what's the second number, and then add 2 to the second number.
So you see here, in the next couple of steps, the second part of the formula is going to work exactly like the first one, but eventually we're going to come out here to do a +2, instead of a +1.
And then finally do the concatenate.
All of this that was built over the course of these 10 steps, finally just becomes one big formula, like that.
Alright, so, you know, you look at this formula, it seems like a lot's going on, but really, you just have to build it one step at a time, starting with "let's isolate from this cell the second number", and we do that by using the dash.
That's how we start with the FIND formula.
Alright, so let's do a little test here, it works great with 1-2, but let's say it starts with something else, like maybe 7-8.
Alright, and see it automatically increments.
Alright, but then I started thinking about, "What if, you don't always want to increase by 2, what if we wanted to follow some sort of a pattern?" Can we build a tougher formula, that may be, like, if we start with 1-5, it will take the 5+1 to start at 6, but then figure out how many to increment?
Alright, and so, that's this whole second part of the formula, that's basically saying: Well hey, what's the first number?
What's the increment?
And then, add the increment to the last number, to make the second number.
So, this big long formula is kind of cool, that it will follow any pattern.
Let's say we start at 7, and we jump up by 3, 7-8-9, 10-11-12, 13-14-15, 16-17-18, but it also works for, and we start at 1001, and we go to 2000.
And it will automatically increment.
So, you know, these complicated formulas, unfortunately, when you just happen upon this, it's like what's going on, they're all really just built one step at a time, like that, and then, you combine it all back together to make a large formula.
Alright, hey, I want to thank Haig for sending that question in, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel!
All right, today's question sent in from Haig, How to make an automatic range counter?
For example, if I start with the number 1-2, I want that to increment to 3-4, and then 5-6! Alright, well this is a complicated formula, but each piece of the formula, is really kind of simple, first thing we want to do is, you want to use the find command to look for that dash in A1.
Right, here, in these small numbers it's always in the second position, but if we get up to the point where it's like 101-102, well, then it's going to be in the fourth position.
So using the FIND is the first piece here.
And then where does the second number start?
Well that's one more than the find, so =FIND()+1 locates where the second number starts, and then what is the second number.
Alright so I use here the MID of A1, this answer, and then I just chose a really big number, like 20, or 10, you know who knows, is this is going to give to millions how far we're going to copy this?
20 is just insanely large, this is going to start at position 3, for a length of 20, which is just going to be, in this case, the digit 2.
And that isolates for me the second number.
And then add one to that, alright, so wherever this cell ended, the next cell is going to start, at one more than that, so that's just a simple +1, right there in my formula.
Alright and then finally, I concatenate a dash, so that's &"-" . At this point we have the first part of the next cell, but then what we have to do, is figure out the second digit.
This is really going to be incredibly similar, we're going to have to go back, figure out where the dash is, figure out where the second number starts, what's the second number, and then add 2 to the second number.
So you see here, in the next couple of steps, the second part of the formula is going to work exactly like the first one, but eventually we're going to come out here to do a +2, instead of a +1.
And then finally do the concatenate.
All of this that was built over the course of these 10 steps, finally just becomes one big formula, like that.
Alright, so, you know, you look at this formula, it seems like a lot's going on, but really, you just have to build it one step at a time, starting with "let's isolate from this cell the second number", and we do that by using the dash.
That's how we start with the FIND formula.
Alright, so let's do a little test here, it works great with 1-2, but let's say it starts with something else, like maybe 7-8.
Alright, and see it automatically increments.
Alright, but then I started thinking about, "What if, you don't always want to increase by 2, what if we wanted to follow some sort of a pattern?" Can we build a tougher formula, that may be, like, if we start with 1-5, it will take the 5+1 to start at 6, but then figure out how many to increment?
Alright, and so, that's this whole second part of the formula, that's basically saying: Well hey, what's the first number?
What's the increment?
And then, add the increment to the last number, to make the second number.
So, this big long formula is kind of cool, that it will follow any pattern.
Let's say we start at 7, and we jump up by 3, 7-8-9, 10-11-12, 13-14-15, 16-17-18, but it also works for, and we start at 1001, and we go to 2000.
And it will automatically increment.
So, you know, these complicated formulas, unfortunately, when you just happen upon this, it's like what's going on, they're all really just built one step at a time, like that, and then, you combine it all back together to make a large formula.
Alright, hey, I want to thank Haig for sending that question in, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel!
