Recently a project I was working on required me to come up with a function to check whether the data being inputted into an Excel field contained letters or numbers. Then, based on the findings it would go ahead & execute another function…
Basically in short it needed to be something like this:
If field contains numbers -> *Do this*
If field contains letters -> *Do this instead*
For an Excel expert it probably sounds fairly simple – but unfortunately an Excel expert is one thing I am not (yet!) so it proved to be a bit of a challenge.
Thankfully I had my trusty steed, AKA Google by my side to help me out which meant I was *eventually* able to get the function up and running… However for what I would assume to be quite a commonly used/required function there seemed to be a lack of good information available on it which is why I decided to put this post together today.
I had to actually visit around 7 different websites before I was able to find a working solution, so hopefully this post helps you out & saves you the cyber legwork.
The function we will using is called “ISNUMBER”, and below you’ll find a working example:
=IF(ISNUMBER(A1), TRUE, FALSE)
So if you input the following formula into say cell B1, you will get a FALSE reading unless a number is entered into cell A1 (you will also get a FALSE reading if it’s left blank).
If you wish for the reading to be TRUE when the field is left blank, then you’ll need to adjust the formula like so:
=IF(OR(ISNUMBER(A1),A1=0), TRUE, FALSE)
When a field is left blank Excel defaults it as being “0”, so what we’ve done here is simply added an OR query to save if a number is entered OR the field is left blank, give a TRUE reading… ELSE give a FALSE reading instead.
I hope that makes sense and is easy enough to follow so far.
Now instead of just echoing the values TRUE and FALSE we’ll look into running a query instead, based on whether or not a letter or number has been inputted.
Here’s how we do it…
=IF(OR(ISNUMBER(A1),A1=0), (A1+10), (A2+20))
It’s pretty simple really – from the above example you’ll notice that I’ve just replaced our TRUE and FALSE values with a basic formula in brackets.
So the function above will look at cell A1, and if cell A1 contains a number (or is left blank) it will add 10 and give you the result. If the cell doesn’t contain a number it will pick the value from A2 and add 20 instead.
Once again I hope you’ve managed to follow that & understand it, but I think you’ll most likely agree it’s pretty easy stuff.
You can adjust it to your needs & make it as complicated or simple as you like. As an example of things getting a bit more complicated, here’s the formula I ended up with in my personal project:
=IF(AND(H14>0, ISNUMBER(H14)), (IF(AND(C21>0,ISNUMBER(C21)), ((C7-C21)+(H14/1000)), ((C7 – (D21+B21)) +(H14/1000)))), IF(AND(C21>0,ISNUMBER(C21)), (C7-C21), (C7-(D21+B21))))
It looks crazy at a glance, but once you break it down it’s not actually that bad (honest)… I was pretty chuffed I was able to get it working as believe it or not this was literally the first Excel query I’d put together in over 10 years.
And just on the note of the formula looking crazy, one thing I would massively recommend when creating Excel formulas is to do them in seperate and make a note of what each part of the formula does. The reason for this is so that when you realize you need to edit it 6 months down the line you can actually work out what’s going on.
I know for a sure fact that if I looked at my formula 6 months down the line without any notes I’d just be sat there scratching my head wondering what on earth I’d put in my cereal the morning I wrote it!
So yeah, hopefully this post helps you out if you end up needing to use a similar function in whatever it is that you might be working on. 🙂