Check a column to see if there are any digits in the code
Bill Method 1:
Flash Fill
Mike Method:
Use the SUBSTITUTE function with an Array Constant.
You will not have to use Ctrl+Shift+Enter because it is an array constant
This will remove one digit at a time
Use the AND function to see if every item in the resultant array is equal to the original item
Bill Method 3:
Use a VBA Function to check for digits
Bill Method 1:
Flash Fill
Mike Method:
Use the SUBSTITUTE function with an Array Constant.
You will not have to use Ctrl+Shift+Enter because it is an array constant
This will remove one digit at a time
Use the AND function to see if every item in the resultant array is equal to the original item
Bill Method 3:
Use a VBA Function to check for digits
Transcript of the video:
Bill: Hey. Welcome back. It's time for another Dueling Excel Podcast.
I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 186: yes if any digits in the cell.
Today’s question is sent in by Jen, has 13,000 rows of data, needs to look through a cell, if any character is a digit, mark it as Yes, otherwise No.
Alright. Well, Mike, I hope that you have some awesome way to do this because I do not.
I am going to use flash fill and, in flash fill, I'm just going to give it a pattern here with some letters and numbers.
I want to make sure that I include all of the possible numbers just so it understands what I'm doing, and 0 like that.
So, there's the original data, and then I'm going to have flash fill fix it for me, and, in the fixed version, we're going to get rid of all the digits.
So, I’ll look and see if there's a digit.
If there is, get rid of it like that, and then press CONTROL+E to flash fill, and what we should have is now all of just the letters, just the letters.
And then the question is, did it change?
So = IF this is = to what we are looking for, then that means that there were no changes, no digits, then we’re saying No, otherwise Yes, like that, and double-click, copy that down, alright, and then anything with a No has no digits there.
We get rid of our original row, and once we copy this, CONTROL+C, ALT+E, S, V, and we can get rid of the flash fill.
Alright, Mike.
Let’s see what you have.
Mike: Wow, MrExcel.
That has to be the most amazing creative unique use of flash fill I think I've ever seen.
You invented a text, took out the actual numbers, CONTROL+E, and it instantly extracted all of the numbers and delivered a new text string without the numbers, and then you did your IF.
Absolutely beautiful.
Alright.
I'm going to come over to this sheet right here and I'm going to use the SUBSTITUTE function.
Now, SUBSTITUTE, I'm going to tell it to look at that text right there, , and the OLD_TEXT I want to find and remove, well, they're all the digits.
So, I'm going to create an array constant { 1 , 2 , 3 , 4 , 5 all of the digits, and }.
Now that's an array constant and it's sitting in OLD_TEXT because I'm not putting a single item there but instead I'm putting a bunch of items.
This is a function argument array operation.
There are 10 different items here which will instruct SUBSTITUTE to deliver 10 separate items, aright, , and if it finds one of those items, what do I want?
“”. That will tell the function to put nothing there, ).
Now, my cursor’s at the end.
When I hit the F9 key, sure enough because there's only a 0, all of these are exactly equal except for the last one.
For the last one, the SUBSTITUTE found the 0 and put nothing in its place.
Now, CONTROL-Z, CONTROL-ENTER and I'm going to copy it down right to here, F2 and F9.
So, if we go to the 6, there's the 5 version, it removed the 5 there, it removed the 6 there, and it removed the 8 there, so there will be 1, 2, 3 different items that are different.
Only when all the items are exactly equal to the original item will it tell us there are no digits.
ESCAPE.
I'm going to come back up to the top.
It sounds like, F2, this is an AND logical test.
AND function.
I want to check whether every single one of those items in that resultant array is = to the original item.
When they're all true, then it will tell me there are no numbers in that text ring.
), CONTROL+ENTER, I get a FALSE because one of them internally is missing that 0.
I'm going to copy this down here.
This one of course will get a TRUE -- same with these -- because all of the internally generated items, if I F2 right here, all of these, F9, are exactly equal to the original.
ESCAPE.
Now, I come up to the top.
By the way, I didn't have to use CONTROL+SHIFT+ENTER because when you use this array constant in your array formula, then you don't have to use CONTROL+SHIFT+ENTER.
Alright.
I'm going to come to the beginning.
That's my logical test.
If all of those come out to be true, , value if true, in ” NO, ” , otherwise please put a YES, ” ). CONTORL+ENTER.
Double click on that.
I'm going to have to double click this one and send it down.
Alright.
That was a little fun with the SUBSTITUTE, an array constant, an AND logical test, and the IF, but, I tell you what, I still cannot believe that flash fill, how you used it to, in essence, extract all of the numbers from that.
Alright, I'm going to throw it back to you, MrExcel.
Bill: Well, that formula with SUBSTITUTE and the array constant and AND, that is freaking amazing.
I had to use flash fill because I couldn't have figured this one out.
That is brilliant.
Now, I do have a third way.
Let's take a look at that.
Now, here's the way that I would really solve this, just a little bit of VBA.
So, I do ALT+F11 to switch over to VBA, INSERT, MODULE, and then type this code.
We're going to create a new function called HASNUMBERS and we're going to pass it to cell value, and we're going to start out with the word ALPHA.
We look at every single character, and if that code, if the [ ASC code – 06:35 ] of that character is between 48 to 57, then we say that it's NUMBERS, EXIT FUNCTION, and just keep going.
Right, so, it looks until it finds a digit.
When it does, it returns HASNUMBERS.
So, here, we’re going to say =HASNUMBERS, point to that cell, and double-click to copy that down.
Anytime it sees a digit over there, will get the NUMBERS, ALPHA, easy to sort them out.
[ =HasNumbers(A2) ] Alright, quick episode recap.
Goal: check the column to see if there are any digits in the character code, the code in the cell.
I used flash fill to remove the digits, then the length function to see if it changed or not.
Mike had a brilliant formula, SUBSTITUTE function with an array constant.
You don't need CONTROL+SHIFT+ENTER.
You’ve got to remove one digit at a time and then use the AND function to look at all 10 results to see if each one is equal to the original item.
Brilliant way to go, and then, my fallback, use a VBA function to check for digits.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and ExcelIsFun.
I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 186: yes if any digits in the cell.
Today’s question is sent in by Jen, has 13,000 rows of data, needs to look through a cell, if any character is a digit, mark it as Yes, otherwise No.
Alright. Well, Mike, I hope that you have some awesome way to do this because I do not.
I am going to use flash fill and, in flash fill, I'm just going to give it a pattern here with some letters and numbers.
I want to make sure that I include all of the possible numbers just so it understands what I'm doing, and 0 like that.
So, there's the original data, and then I'm going to have flash fill fix it for me, and, in the fixed version, we're going to get rid of all the digits.
So, I’ll look and see if there's a digit.
If there is, get rid of it like that, and then press CONTROL+E to flash fill, and what we should have is now all of just the letters, just the letters.
And then the question is, did it change?
So = IF this is = to what we are looking for, then that means that there were no changes, no digits, then we’re saying No, otherwise Yes, like that, and double-click, copy that down, alright, and then anything with a No has no digits there.
We get rid of our original row, and once we copy this, CONTROL+C, ALT+E, S, V, and we can get rid of the flash fill.
Alright, Mike.
Let’s see what you have.
Mike: Wow, MrExcel.
That has to be the most amazing creative unique use of flash fill I think I've ever seen.
You invented a text, took out the actual numbers, CONTROL+E, and it instantly extracted all of the numbers and delivered a new text string without the numbers, and then you did your IF.
Absolutely beautiful.
Alright.
I'm going to come over to this sheet right here and I'm going to use the SUBSTITUTE function.
Now, SUBSTITUTE, I'm going to tell it to look at that text right there, , and the OLD_TEXT I want to find and remove, well, they're all the digits.
So, I'm going to create an array constant { 1 , 2 , 3 , 4 , 5 all of the digits, and }.
Now that's an array constant and it's sitting in OLD_TEXT because I'm not putting a single item there but instead I'm putting a bunch of items.
This is a function argument array operation.
There are 10 different items here which will instruct SUBSTITUTE to deliver 10 separate items, aright, , and if it finds one of those items, what do I want?
“”. That will tell the function to put nothing there, ).
Now, my cursor’s at the end.
When I hit the F9 key, sure enough because there's only a 0, all of these are exactly equal except for the last one.
For the last one, the SUBSTITUTE found the 0 and put nothing in its place.
Now, CONTROL-Z, CONTROL-ENTER and I'm going to copy it down right to here, F2 and F9.
So, if we go to the 6, there's the 5 version, it removed the 5 there, it removed the 6 there, and it removed the 8 there, so there will be 1, 2, 3 different items that are different.
Only when all the items are exactly equal to the original item will it tell us there are no digits.
ESCAPE.
I'm going to come back up to the top.
It sounds like, F2, this is an AND logical test.
AND function.
I want to check whether every single one of those items in that resultant array is = to the original item.
When they're all true, then it will tell me there are no numbers in that text ring.
), CONTROL+ENTER, I get a FALSE because one of them internally is missing that 0.
I'm going to copy this down here.
This one of course will get a TRUE -- same with these -- because all of the internally generated items, if I F2 right here, all of these, F9, are exactly equal to the original.
ESCAPE.
Now, I come up to the top.
By the way, I didn't have to use CONTROL+SHIFT+ENTER because when you use this array constant in your array formula, then you don't have to use CONTROL+SHIFT+ENTER.
Alright.
I'm going to come to the beginning.
That's my logical test.
If all of those come out to be true, , value if true, in ” NO, ” , otherwise please put a YES, ” ). CONTORL+ENTER.
Double click on that.
I'm going to have to double click this one and send it down.
Alright.
That was a little fun with the SUBSTITUTE, an array constant, an AND logical test, and the IF, but, I tell you what, I still cannot believe that flash fill, how you used it to, in essence, extract all of the numbers from that.
Alright, I'm going to throw it back to you, MrExcel.
Bill: Well, that formula with SUBSTITUTE and the array constant and AND, that is freaking amazing.
I had to use flash fill because I couldn't have figured this one out.
That is brilliant.
Now, I do have a third way.
Let's take a look at that.
Now, here's the way that I would really solve this, just a little bit of VBA.
So, I do ALT+F11 to switch over to VBA, INSERT, MODULE, and then type this code.
We're going to create a new function called HASNUMBERS and we're going to pass it to cell value, and we're going to start out with the word ALPHA.
We look at every single character, and if that code, if the [ ASC code – 06:35 ] of that character is between 48 to 57, then we say that it's NUMBERS, EXIT FUNCTION, and just keep going.
Right, so, it looks until it finds a digit.
When it does, it returns HASNUMBERS.
So, here, we’re going to say =HASNUMBERS, point to that cell, and double-click to copy that down.
Anytime it sees a digit over there, will get the NUMBERS, ALPHA, easy to sort them out.
[ =HasNumbers(A2) ] Alright, quick episode recap.
Goal: check the column to see if there are any digits in the character code, the code in the cell.
I used flash fill to remove the digits, then the length function to see if it changed or not.
Mike had a brilliant formula, SUBSTITUTE function with an array constant.
You don't need CONTROL+SHIFT+ENTER.
You’ve got to remove one digit at a time and then use the AND function to look at all 10 results to see if each one is equal to the original item.
Brilliant way to go, and then, my fallback, use a VBA function to check for digits.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and ExcelIsFun.
