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, ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
    Diamond Contributor
    May 04, 2018

    Hi Mike,

     

    The formula extracts first set of continuous digits in the string. I bit simplified it using the idea described here http://www.excelhowto.com/how-to-find-the-first-number-in-a-text-string-use-excel-formula/

    =AGGREGATE(14,6,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))),1)

    To your number you may apply custom format (Ctrl+1) like this

    [>9999]0"ft" 00"inch x" 0"ft" 0"inch";0"ft" 0"inch x" 0"ft" 0"inch"

    but that's only as example, I don't know real logic of your numbers. More about custom number formats is here (first what found) http://simoncpage.co.uk/blog/2008/09/excel-custom-and-conditional-number-formatting/

    Result looks like

    and attached

    • Mike Emberley's avatar
      Mike Emberley
      Copper Contributor
      May 10, 2018

      The Aggregate function formula worked like a charm, thank you very much.  I tried the other "mid" formula and that also worked to a point.  Could you explain the logic that excel uses in the Find(char(row fields, as I tried changing it to a1 to a319 to include the whole data set, but it displayed a blank, all the way to A49.  From A50 through A56 it displayed a 1, from A57 and higher it worked as expected.  I've attached the spreadsheet if needed.  

       

       =MID(A3, AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A3),1), AGGREGATE(15,6, FIND(CHAR(ROW($A$58:$A$126)), RIGHT(A3, LEN(A3)-AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A3),1) +1) ),1 )-1 )

Resources