Siva has a cell with comma delimited numbers. How can you add 500 to each number in the cell? In this episode of the Dueling Excel podcast, Bill uses a tiny VBA macro from Excel MVP Brad Yundt. Mike builds a killer formula using the LET function in Excel.
Transcript of the video:
Hey hey, welcome back! It is time for another Dueling Excel podcast.
This is our episode 192. Add 500 to a single cell that says 123, 124, 125.
I am Bill Jelen from MrExcel.
I will be joined by Mike Girvin from ExcelisFun. Let's get started.
Oh Mike, here is a great one. This was a comment on our Dueling Excel podcast from last week.
Siva says in one cell he has 700, 701, 702, 703, 704, 705.
And you want to add 500 to those.
So in other words it would be in one cell 1200, 1201, 1202, 1203, 1204, 1205.
I can think of a couple of different ways to do this. My way, just like the theme song says.
I am going to go with VBA. And actually it's not my VBA.
It is Brad Yundt's VBA. This is from my episode 2317.
Brad lent me this three-line code and I am going to reuse that Splitter code here.
So what I do is I switch over to VBA with Alt+F11. Then Insert, Module.
And in that module i'm going to put this simple little three- line UDF function. Splitter. Text as String. Delimiter as String.
That's going to return a variant. Splitter is equal to the SPLIT.
SPLIT is a function in VBA. The SPLIT of Text by the Delimiter.
End Function. It is really simple. Alright. So, if we come back here.
I say =SPLITTER of this original text.
Split by the comma. We could have hard-coded the comma in the UDF, I guess.
Check this out. It gives me a horizontal array.
Which is perfect. Then what I am going to do.
I am going to take the SPLITTER() plus whatever is in this cell here.
I will press F4. Check that out.
It adds 500 to all of those items.
But we want that all back in one cell so we're going to come here and say =TEXTJOIN.
The delimiter is going to be a comma. Ignore empty? Sure.
I don't think we're gonna have any empty. But in case we do, then yes.
Then the SPLITTER. And Enter.
Perfect! Just copy that down.
Copy that down and if for some weird reason we had different numbers here.
So let's do =RANDBETWEEN(1,7) times 100.
All we would have to do is take the dollar signs out. The $C$8 could just become C8 like that.
Copy that down. And now we are adding 700 to the 1190. Check that out.
Great way to go. IF you are allowed to use VBA.
Mike. let's see what you have.
[ Mike Girvin ] Thanks, MrExcel! Ding Ding Ding! VBA wins. Can you believe it?
There's a SPLIT function?
If only we had that in the worksheet! VBA gets the point for this duel.
Now all of you fans of the ExcelIsFun channel. You gotta do me a favor.
In the Channel section of my channel.
There's the number one Excel video guy in the history of the world. Yes, MrExcel.com.
You got to go to his channel.
Bill MrExcel Jelen is the original Excel video guy posting videos at iTunes and a few older videos from the year 2006 at YouTube.
You have to go and Subscribe and click that bell.
We gotta pump them up to a hundred thousand subscribers.
So go and subscribe and then enjoy lots of Historic Excel Awesomeness.
Now, anytime we do something like this.
It depends on what the pattern and the data is.
Now I assume that he just gave this example quick. Started at a number. Adding one.
And all the numbers have the same number of characters.
If that is the pattern then all we need to do is do a SEQUENCE.
Start at 1444.
Add one to that to get each one of these into that sequence of number. Then we add this amount and join them.
The only trick is we need to know how many numbers there are.
Inside of SEQUENCE.
That little construction there tells us how many commas there are plus one.
So if I hit F9 that means there is nine. That's how many rows we want.
And then the starting position is, well, i'm extracting that first number. And the Step is "add one".
And then to that SEQUENCE ,F9. We simply add whatever the amount is.
And TEXTJOIN. But of course, those aren't safe assumptions.
There might be different numbers in here. They might be different lengths.
So I want to use the LET function to try and create this little construction. MID is going to look over here.
The tricky part.
How in the world for start number, do I generate an array of all of the starting positions?
1, 6, 11 and so on.
Again these have a uniform increment. But. we are going to assume that that's not the case.
Then how do I create the array for number of characters to extract for each starting point. Now, I am going to use the function LET.
Which allows us to define variables and then use those variables throughout the formula. There's a variable name. There's the cell.
Number of Characters is the name of a variable.
We'll count how many characters are in the cell. 44 are in the first one.
There's the name of the third variable.
Sequence will give us the number 1 to 44.
Now the next variable is going to be Comma Error.
But before we get to that actual calculation.
We first need to extract every single character and analyze it to see if it's a comma. Now.
I am going to just look and see what this evaluates to by taking the variable name and putting it as the final calculation.
When I hit enter, you can see that we extracted it.
However, since I ultimately need the starting positions of the numbers I am going to add a comma to the front of it.
So in double quotes, a comma. And then join it to whatever's in the cell.
And that way, now I have a marker for the first position and the sixth position.
F2. I want to get an error where there are commas or text. So I add zero.
The zero will be added to the numbers.
But I'll get an error where there are commas. And I want a TRUE there.
So I am going to use the ISERROR function.
What we are doing here is we are building it one step at a time. And seeing what it evaluates to. There we go.
Now we have a marker. Comma comma comma.
Now the fifth variable is going to be Comma Position. Watch this.
I am going to take the FILTER function and we're going to filter the sequence 1 to 44. Comma. And then will be Comma Error.
So that has the pattern of TRUEs and FALSEs.
Then at the end we can paste the variable we just created. Close parentheses. And see what it evaluates to.
Oops! I left this one in. I do that a lot. I am going to take that out.
Now, that's the array of starting positions for MID.
Now the hard part is getting the difference between six and one. Eleven and six.
And here is how I would do it if I was using worksheet formulas.
I would take everything with one cell below and subtract the full array.
And then I would ask, "hey, which one is negative?" And then put some number there.
So inside LET I am actually going to filter this array and remove the first row.
That way, I get from 6 to 41.
So the variable is going to be called Remove First Row. I am going to use FILTER on the Comma Position.
And I am going to use SEQUENCE COUNTing Comma Position.
Which delivers one to however many positions we have.
And I want all of them greater than one.
So that filter result would look like this. Missing the first row.
The Seventh variable.
I am going to call Pre-Length Array because the next calculation is calculating the length of each number.
I simply took Remove First Row minus Comma Position minus 1. And we can see what that gives us.
There is an error. That is our marker that that's the last one.
But they are all 4. Later, when we change one of these.
These will be dynamic.
Now for the actual length we'll take ISNA of that array. And there's only one of them. So, that's the last one.
And I am going to put some big number.
I know that none of the numbers are ever going to be longer than 5. So that will work.
Otherwise, the Pre-length Array which has everything except for that error at the end.
So now as a Lengths variable, that's what it looks like. Now we have the two arrays.
We need Start Position and Length. The ninth variable is called Extract.
We do MID of the Cell. There's the Comma Positions.
Lengths. And that'll give us numbers.
And we simply add.
And it shouldn't be C15 it should be C8.
And so now let's see what Extract gives us. There's our numbers. The tenth variable is called Final.
There's the calculation. We are simply joining them.
And now, we'll see if this works. Boom! There it is.
Now I can double-click on LET and send it down. And this one.
If i change the length. That is working perfectly. All right. I will throw it back to MrExcel.
[ Bill ] Hey, Mike. Thanks for the shout-out.
I am trying to get those hundred thousand subscribers . For any one on my channel.
I am sure you're already subscribed to ExcelIsFun.
But if not, please subscribe to mike's channel.
He's been around almost as long as I have. He has just an amazing number of videos.
Every month he is Coming out with more and more and more .
So, hey, thanks to all of you who can Subscribe. We both appreciate it.
In fact, give all of our friends down there a Subscribe.
Mike, you completely surprised me here. I teed this one up for you.
I used VBA. I was sure you were going to use Power Query. I have a Power Query way.
I am sure you have a better Power Query way.
So, hey, next Friday, let's come back and solve this exact same problem.
But with Power Query. Your first formula down here.
It actually answers the problem the way that it was described. But you are right.
Siva is probably just simplifying the question for us.
They won't really be in sequence. But I love this formula here.
The formula up here actually makes my head spin.
I had to go take some Tylenol after that one. It is crazy.
I actually feel bad sending you this question.
I figured it would be a few minutes in Power Query.
This formula had to take a couple of days to figure out.
So my complete respect to you for that awesome formula.
Hoorah! All right, well, hey, I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
[ Drum roll ].
[ ♫♫ ] Formulas? or VBA?! Ctrl+Shift+Enter or a Macro way?
[ ♫ ] There's Mike G on the CSE! Billy J getting down with the VBA! [ ♫ ].
[ ♫ ] Oh My! It's the Dueling Excel guys! [ ♫ ].
[ ♫ ] Stand by! It's Dueling Excel Time [ ♫ ].
[ ♫ ] My my! It's Dueling Excel Time! [ ♫ ].
[ Applause ]
This is our episode 192. Add 500 to a single cell that says 123, 124, 125.
I am Bill Jelen from MrExcel.
I will be joined by Mike Girvin from ExcelisFun. Let's get started.
Oh Mike, here is a great one. This was a comment on our Dueling Excel podcast from last week.
Siva says in one cell he has 700, 701, 702, 703, 704, 705.
And you want to add 500 to those.
So in other words it would be in one cell 1200, 1201, 1202, 1203, 1204, 1205.
I can think of a couple of different ways to do this. My way, just like the theme song says.
I am going to go with VBA. And actually it's not my VBA.
It is Brad Yundt's VBA. This is from my episode 2317.
Brad lent me this three-line code and I am going to reuse that Splitter code here.
So what I do is I switch over to VBA with Alt+F11. Then Insert, Module.
And in that module i'm going to put this simple little three- line UDF function. Splitter. Text as String. Delimiter as String.
That's going to return a variant. Splitter is equal to the SPLIT.
SPLIT is a function in VBA. The SPLIT of Text by the Delimiter.
End Function. It is really simple. Alright. So, if we come back here.
I say =SPLITTER of this original text.
Split by the comma. We could have hard-coded the comma in the UDF, I guess.
Check this out. It gives me a horizontal array.
Which is perfect. Then what I am going to do.
I am going to take the SPLITTER() plus whatever is in this cell here.
I will press F4. Check that out.
It adds 500 to all of those items.
But we want that all back in one cell so we're going to come here and say =TEXTJOIN.
The delimiter is going to be a comma. Ignore empty? Sure.
I don't think we're gonna have any empty. But in case we do, then yes.
Then the SPLITTER. And Enter.
Perfect! Just copy that down.
Copy that down and if for some weird reason we had different numbers here.
So let's do =RANDBETWEEN(1,7) times 100.
All we would have to do is take the dollar signs out. The $C$8 could just become C8 like that.
Copy that down. And now we are adding 700 to the 1190. Check that out.
Great way to go. IF you are allowed to use VBA.
Mike. let's see what you have.
[ Mike Girvin ] Thanks, MrExcel! Ding Ding Ding! VBA wins. Can you believe it?
There's a SPLIT function?
If only we had that in the worksheet! VBA gets the point for this duel.
Now all of you fans of the ExcelIsFun channel. You gotta do me a favor.
In the Channel section of my channel.
There's the number one Excel video guy in the history of the world. Yes, MrExcel.com.
You got to go to his channel.
Bill MrExcel Jelen is the original Excel video guy posting videos at iTunes and a few older videos from the year 2006 at YouTube.
You have to go and Subscribe and click that bell.
We gotta pump them up to a hundred thousand subscribers.
So go and subscribe and then enjoy lots of Historic Excel Awesomeness.
Now, anytime we do something like this.
It depends on what the pattern and the data is.
Now I assume that he just gave this example quick. Started at a number. Adding one.
And all the numbers have the same number of characters.
If that is the pattern then all we need to do is do a SEQUENCE.
Start at 1444.
Add one to that to get each one of these into that sequence of number. Then we add this amount and join them.
The only trick is we need to know how many numbers there are.
Inside of SEQUENCE.
That little construction there tells us how many commas there are plus one.
So if I hit F9 that means there is nine. That's how many rows we want.
And then the starting position is, well, i'm extracting that first number. And the Step is "add one".
And then to that SEQUENCE ,F9. We simply add whatever the amount is.
And TEXTJOIN. But of course, those aren't safe assumptions.
There might be different numbers in here. They might be different lengths.
So I want to use the LET function to try and create this little construction. MID is going to look over here.
The tricky part.
How in the world for start number, do I generate an array of all of the starting positions?
1, 6, 11 and so on.
Again these have a uniform increment. But. we are going to assume that that's not the case.
Then how do I create the array for number of characters to extract for each starting point. Now, I am going to use the function LET.
Which allows us to define variables and then use those variables throughout the formula. There's a variable name. There's the cell.
Number of Characters is the name of a variable.
We'll count how many characters are in the cell. 44 are in the first one.
There's the name of the third variable.
Sequence will give us the number 1 to 44.
Now the next variable is going to be Comma Error.
But before we get to that actual calculation.
We first need to extract every single character and analyze it to see if it's a comma. Now.
I am going to just look and see what this evaluates to by taking the variable name and putting it as the final calculation.
When I hit enter, you can see that we extracted it.
However, since I ultimately need the starting positions of the numbers I am going to add a comma to the front of it.
So in double quotes, a comma. And then join it to whatever's in the cell.
And that way, now I have a marker for the first position and the sixth position.
F2. I want to get an error where there are commas or text. So I add zero.
The zero will be added to the numbers.
But I'll get an error where there are commas. And I want a TRUE there.
So I am going to use the ISERROR function.
What we are doing here is we are building it one step at a time. And seeing what it evaluates to. There we go.
Now we have a marker. Comma comma comma.
Now the fifth variable is going to be Comma Position. Watch this.
I am going to take the FILTER function and we're going to filter the sequence 1 to 44. Comma. And then will be Comma Error.
So that has the pattern of TRUEs and FALSEs.
Then at the end we can paste the variable we just created. Close parentheses. And see what it evaluates to.
Oops! I left this one in. I do that a lot. I am going to take that out.
Now, that's the array of starting positions for MID.
Now the hard part is getting the difference between six and one. Eleven and six.
And here is how I would do it if I was using worksheet formulas.
I would take everything with one cell below and subtract the full array.
And then I would ask, "hey, which one is negative?" And then put some number there.
So inside LET I am actually going to filter this array and remove the first row.
That way, I get from 6 to 41.
So the variable is going to be called Remove First Row. I am going to use FILTER on the Comma Position.
And I am going to use SEQUENCE COUNTing Comma Position.
Which delivers one to however many positions we have.
And I want all of them greater than one.
So that filter result would look like this. Missing the first row.
The Seventh variable.
I am going to call Pre-Length Array because the next calculation is calculating the length of each number.
I simply took Remove First Row minus Comma Position minus 1. And we can see what that gives us.
There is an error. That is our marker that that's the last one.
But they are all 4. Later, when we change one of these.
These will be dynamic.
Now for the actual length we'll take ISNA of that array. And there's only one of them. So, that's the last one.
And I am going to put some big number.
I know that none of the numbers are ever going to be longer than 5. So that will work.
Otherwise, the Pre-length Array which has everything except for that error at the end.
So now as a Lengths variable, that's what it looks like. Now we have the two arrays.
We need Start Position and Length. The ninth variable is called Extract.
We do MID of the Cell. There's the Comma Positions.
Lengths. And that'll give us numbers.
And we simply add.
And it shouldn't be C15 it should be C8.
And so now let's see what Extract gives us. There's our numbers. The tenth variable is called Final.
There's the calculation. We are simply joining them.
And now, we'll see if this works. Boom! There it is.
Now I can double-click on LET and send it down. And this one.
If i change the length. That is working perfectly. All right. I will throw it back to MrExcel.
[ Bill ] Hey, Mike. Thanks for the shout-out.
I am trying to get those hundred thousand subscribers . For any one on my channel.
I am sure you're already subscribed to ExcelIsFun.
But if not, please subscribe to mike's channel.
He's been around almost as long as I have. He has just an amazing number of videos.
Every month he is Coming out with more and more and more .
So, hey, thanks to all of you who can Subscribe. We both appreciate it.
In fact, give all of our friends down there a Subscribe.
Mike, you completely surprised me here. I teed this one up for you.
I used VBA. I was sure you were going to use Power Query. I have a Power Query way.
I am sure you have a better Power Query way.
So, hey, next Friday, let's come back and solve this exact same problem.
But with Power Query. Your first formula down here.
It actually answers the problem the way that it was described. But you are right.
Siva is probably just simplifying the question for us.
They won't really be in sequence. But I love this formula here.
The formula up here actually makes my head spin.
I had to go take some Tylenol after that one. It is crazy.
I actually feel bad sending you this question.
I figured it would be a few minutes in Power Query.
This formula had to take a couple of days to figure out.
So my complete respect to you for that awesome formula.
Hoorah! All right, well, hey, I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
[ Drum roll ].
[ ♫♫ ] Formulas? or VBA?! Ctrl+Shift+Enter or a Macro way?
[ ♫ ] There's Mike G on the CSE! Billy J getting down with the VBA! [ ♫ ].
[ ♫ ] Oh My! It's the Dueling Excel guys! [ ♫ ].
[ ♫ ] Stand by! It's Dueling Excel Time [ ♫ ].
[ ♫ ] My my! It's Dueling Excel Time! [ ♫ ].
[ Applause ]
