Forum Discussion

Mike Emberley's avatar
Mike Emberley
Copper Contributor
May 03, 2018

Need help with formula IF,ISNUMBER

I am being perplexed on how to accomplish something that would seem easy on the surface, but is bugging me to no end now.  I have a dataset that contains a code that consists of letters and numbers, the numbers are a measurement that is feet and inches.  I used IF and ISnumber functions to correctly reference the correct cell to display, until I noticed that 11068 actually contains 1068 in it, so it references the cell that corresponds to 1068.  I thought of extracting the numbers but some codes contain other random numbers.  Also the numbers do not start at the same position either.  I tried putting in the specific letters before any 1068 codes into the formula and the results just say False now.  Here is an example of the formula used...

 

=IF(ISNUMBER(FIND("A1068",A21)),$B$3,IF(ISNUMBER(FIND("M1068",A21)),$B$4 (this is just part of the formula, there are 26 different numeric possibilities so the formula is very long.)  Before I added the specific letter before "1068" it came up with valid answers, just incorrect in the case of "11068" because it contained "1068" in it. 

 

My dataset looks like this..... 

 

XXXX1068XXXX

XXXXX11068XX2XX

 

Any help would be greatly appreciated!

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 03, 2018

    Hi Mike,

     

    You may check this https://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html discussion, here are few formulas to extract numbers from text string, similar ones are in other places.

     

    If you are Office365 subscriber you may use TEXTJOIN like

    =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

    (array formula, Ctrl+Shift+Enter), don't remember from where it is in my collection, but it's more or less clear. The only point for your second example it will return 110682, not sure if that's your case or not. Or you'd like to extract only first number in the text?

    • Mike Emberley's avatar
      Mike Emberley
      Copper Contributor
      May 04, 2018
      I am only interested in the string of digits that are together, 4 or 5 characters long. Unfortunately for me the way my industry measures things in feet and inches instead of just inches has made this case difficult. So the formula I tried is giving the value for the 1ft 0 inch 6ft 8inch door (1068) when the door is actually a 1ft 10inch 6ft 8 inch door (11068). Was trying to figure a way in excel to have it figure out what the size of this door is based on the characters in our Item number from our database. Also was trying to have one magic formula do this, but maybe that is too complex?

      Is there a way to extract only the numbers that are together when I know the starting position, and I can just change the starting position where the numbers appear differently, but the exact number of digits will be either 4 or 5. ***And as I write this I read below and this is what is done!***

      I will try the aggregate solution below and see if it works! Thank you!
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 03, 2018

    And if to find only first number in text that could be like (not array formula)

    =MID(A1,
   AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1),
   AGGREGATE(15,6,
      FIND(CHAR(ROW(A58:A126)),
          RIGHT(A1, LEN(A1)-AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1) +1) 
      ),1
   )-1
)

    First AGGREGATE finds the position of first digit in the string; second one the position of first letter or special character in the rest of the text, finally MID between them.

Resources