MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find Any Digits


October 20, 2017 - by Bill Jelen

Find Any Digits

Excel - how to keep only the digits from a cell, not the letters.


Watch Video

  • 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

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm bill jelling
  • from MrExcel I'll be joined by Mike
  • Irvin from Excel as fun as our episode
  • 186 yes if any digits in the cell today
  • these questions and then by Jen has
  • 13,000 rows of data means to look
  • through herself any character is a digit
  • market is yes otherwise no well Mike I
  • hope that you have some awesome way to
  • do this because I do not I am gonna use
  • flash fill and in flash fill I'm just
  • gonna give it a pattern here with some
  • letters and numbers make sure that
  • include all of the possible numbers just
  • so it understands what I'm doing and
  • zero like that so there's the original
  • data and then I'm gonna have flash fill
  • fix it for me and in the fixed version
  • we're gonna get rid of all the digits so
  • look and see if there's a digit if there
  • is get rid of it like that and then
  • press ctrl e to flash film and what we
  • should have is now all of just the
  • letters just the letters and then the
  • question is did it change so equal if
  • this is equal to what we were looking
  • for then that means that there were no
  • changes no digits and we're saying no
  • otherwise yes like that and double click
  • and copy that down alright and then
  • anything with an O has no digits there
  • when geared of our original row and once
  • we copy this control see all esv and we
  • can get rid of the flash fill all right
  • Mike let's see we have Wow MrExcel
  • that has to be the most amazing creative
  • unique use of flash fill I think I've
  • ever seen you invented the text took out
  • the actual numbers control a and it
  • instantly extracted all of the numbers
  • and deliver
  • new text string without the numbers and
  • that you did your if absolutely
  • beautiful alright I'm gonna come over to
  • this sheet right here and I'm gonna use
  • the substitute function now substitute
  • I'm going to tell it to look at that
  • text right there comma 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 curly bracket 1 comma 2
  • comma 3 comma 4 comma 5 all of the
  • digits and curly bracket now that's an
  • array constant and it's sitting an 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
  • all right comma and if it finds one of
  • those items what do I want double quote
  • double quote that will tell the function
  • to put nothing there close parenthesis
  • now my cursors 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 ctrl Z ctrl
  • enter and I'm going to copy it down
  • right to here F 2 and F 9 so if we go to
  • the 6 there's the five version it
  • removed the 5 there it removed the 6
  • there and it removed the 8 there so
  • there will be one to three 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 equal to the
  • original item when they're all true then
  • it will tell me there are no numbers in
  • that text ring close parentheses control
  • enter I get a false because one of them
  • internally is missing that zero I'm
  • gonna copy this down here this one of
  • course will get a true same with these
  • because all of the internally generated
  • items if I'm f2 right here all of these
  • f9 are exactly equal to the original
  • escaped 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
  • all right I'm gonna come to the
  • beginning that's my logical test if all
  • of those come out to be true comma value
  • if true and double quotes no and don't
  • quote comma otherwise please put a yes
  • and close parentheses control enter
  • double click on that I'm gonna have to
  • double click this one and send it down
  • all right that was a little fun with the
  • substitute an array constant and an
  • illogical test and the if but I tell you
  • what I still cannot believe that flash
  • fill how you used it to an essence
  • extract all of the numbers from that all
  • right I'm gonna throw it back to you mr.
  • Excel well that formula with substitute
  • and the rate constant and and that is
  • freaking amazing I had to use flash fill
  • because I couldn't have feared this one
  • out that is Brandt now I do have a third
  • way let's take a look at that you know
  • 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 a
  • module and then type this code we're
  • gonna create a new function called has
  • numbers and we're gonna pass it a cell
  • value and we're gonna start out with a
  • word alpha we look at every single
  • character and if that code and the
  • keycode of that characters between 48 to
  • 57 then we say that it's numbers exit
  • function and just keep going right so it
  • looks until it finds the digit when it
  • does it returns has number so here when
  • I say equal has numbers point to that
  • cell and double-click to copy that down
  • any time it sees the digit over there
  • we'll get the numbers alpha easy to sort
  • them out alright quick episode recap
  • goal check it column to see if there are
  • any digits in the character code or at
  • the code in the in the cell I use flash
  • Ville to remove the digits then the
  • length function to see if it changed or
  • not Mike had a brilliant formula
  • substitute function within a rate
  • constant
  • you don't need control shift enter it'll
  • 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 way I want to thank
  • everyone for stopping by we'll see you
  • next time for another net cast from mr.
  • Excel and Excel is fun

Download File

Download the sample file here: Duel186.xlsm

Title Photo: Pexels / Pixabay