+ Reply to Thread
Results 1 to 17 of 17

Extract largest number in an alphanumerical string

  1. #1
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Extract largest number in an alphanumerical string

    I need to come up with a formula that can take the larger of two numbers that are in an alphanumerical string. These are dimensions, and I need to get the biggest of the two. Thanks in advance for your help!


    For instance:

    input:
    A1: 10"X12"
    A2: 14"X10"
    A3: 14"X16"

    output:
    B1: 12
    B2: 14
    B3: 16
    Last edited by chitownsox14; 03-15-2011 at 12:16 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Extract largest number in an alphanumerical string

    Hi chitownsox14 and welcome to the forum.
    Put this formula in B1 and pull down.
    Please Login or Register  to view this content.
    I'm sure there are easier formulas than mine but I do believe it will work for your work.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    Thanks for the quick reply, but that didnt work for me. looking at the formula, I think what you tried to do was find the " and the x. However, in this particular spreadsheet, there are multiple formats. I should have been clearer, but this is what I am dealing with:

    A1: 32X45
    A2: 16"Wx20"H
    A3: 10"X12"

    and I want the output to be:

    A1: 45
    A2: 20
    A3:12

    I have been able to get the first number in the alphanumerical series with this code

    =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))


    but I cant figure out how to get the second number to compare them in a max function...Please help!

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract largest number in an alphanumerical string

    One way
    In B1 (Helper Column)
    Please Login or Register  to view this content.
    This nested function can be increased or decreased as required
    In C1
    Please Login or Register  to view this content.

    If you really want to you can combine these functions, but I wouldn't bother
    Please Login or Register  to view this content.
    Hope this helps
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    Wow...you are amazing. It worked like a charm. Thank you so much!

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract largest number in an alphanumerical string

    On behalf of all, happy to have helped.

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Extract largest number in an alphanumerical string

    Hi,

    you could try, if in A2 the string,

    in B2

    =INDEX(INDEX(ROW($A$1:$A$10000),),MATCH(99^99,SEARCH("*"&ROW($A$1:$A$10000)&"*",A2)))
    to be confirmed with control+shift+enter.

    The formula should extract the biggest number ( or a single number) contained into the string: ROW(A1:A10000) permits to extract only numbers from 1 to 10000. You can adjust the formula.

    Regards


    ----
    Edit: sorry, too late. One Index is enough:

    =INDEX(ROW($A$1:$A$10000),MATCH(99^99,SEARCH("*"&ROW($A$1:$A$10000)&"*",A2)))
    Attached Files Attached Files
    Last edited by canapone; 03-15-2011 at 10:18 AM. Reason: Lost in traduction

  8. #8
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Extract largest number in an alphanumerical string

    CSE formula
    for min =MIN(IF(ISERROR(MID($A$1,ROW(INDIRECT("1:"&LEN(A1)-1)),2)*1),10000,MID($A$1,ROW(INDIRECT("1:"&LEN(A1)-1)),2)*1))

    for max =MAX(IF(ISERROR(MID($A$1,ROW(INDIRECT("1:"&LEN(A1)-1)),2)*1),0,MID($A$1,ROW(INDIRECT("1:"&LEN(A1)-1)),2)*1))

  9. #9
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    I just noticed a problem that i didnt before....the people that input these numbers are so inconsistant that it is frustrating. Some of the dimensions say 8" diam. For example:

    A1: 32X45
    A2: 16"Wx20"H
    A3: 10"X12"
    A4: 8" Diam

    and I want the output to be:

    B1: 45
    B2: 20
    B3:12
    B4: 8


    Quote Originally Posted by Marcol View Post
    One way
    In B1 (Helper Column)
    Please Login or Register  to view this content.
    This nested function can be increased or decreased as required
    In C1
    Please Login or Register  to view this content.

    If you really want to you can combine these functions, but I wouldn't bother
    Please Login or Register  to view this content.
    Hope this helps

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract largest number in an alphanumerical string

    Good thinking CANAPONE, I knew there had to be a better way.

    chitownsox14 go with CANAPONE

  11. #11
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    Thank you so much everyone! you guys were very helpful. The index one worked for all my scenarios. Thanks again!

  12. #12
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    I ran into another problem...Because im dealing with over 2000 data inputs and then doing a ton of other calculations, my work computer cant handle the index function. i combined it with the if(iserror(index),0,index) and when i autofill all the way down, excel crashes. Is there a way that requires less computational power?


    Quote Originally Posted by Marcol View Post
    Good thinking CANAPONE, I knew there had to be a better way.

    chitownsox14 go with CANAPONE

  13. #13
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Extract largest number in an alphanumerical string

    Hi,

    if the number you have to extract is a one-two digit number (one Index is enough)


    =INDEX(ROW($A$1:$A$100),MATCH(99^99,SEARCH("*"&ROW($A$1:$A$100)&"*",A2)))
    to be confirmed with control+shift+enter.

    I don't think it's a great step forward, but maybe it could be much lighter to be processed in large amounts.


    Hope it helps

    Regards
    Last edited by canapone; 03-15-2011 at 10:44 AM.

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract largest number in an alphanumerical string

    @ CANAPONE
    120"x48", 120"x48" are common builders' imperial (non-metric) sizes in inches, equivalent to 2440mm x1220mm3050mm x 1520mm

    Might be better to stick with your original formula

  15. #15
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract largest number in an alphanumerical string

    @ CANAPONE
    120"x60" and 108"x48" are common builders' sizes (Imperial inches) in the UK
    The equivalent metric size is 3050x1520mm and 2740x1220mm approx.
    The USA it is still non-metric I think.
    Might be best to stick with your original formula

    [EDIT]
    Apologies for apparent double post, got "database error" when I sent the first one, I thought it failed to send.
    Last edited by Marcol; 03-15-2011 at 11:08 AM.

  16. #16
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Extract largest number in an alphanumerical string

    How about a variation of Sadath31's formula? This seems to allow for really big numbers.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-15-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extract largest number in an alphanumerical string

    Thanks again guys. I used the index but lowered the number from 10000 to 3000. that did the trick! thanks again. Hopefully this covers everything.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1