Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Extract Number From Alphanumeric Text

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Extract Number From Alphanumeric Text

    To extract the numeric values from an alpha numeric text, use any of the following array formulas

    {=SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))}

    OR

    {=SUM(IFERROR(MID(A1,LARGE(ISNUMBER(MID(A1,ROW(IND IRECT("1:"&LEN(A1))),1)^0)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)))),1),0)*(1&REPT("0",(ROW(INDIRECT("1:"&LEN(A1)))-1))))}
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    A link that shows hows to extract numbers that are placed together. Ex. ABC123DEF

    http://www.familycomputerclub.com/ex...eric-text.html
    Last edited by Rick Rothstein; 02-25-2012 at 07:50 AM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    And to illustrate how this code works

    Consider the case fdsff43t59r178dq, where the output expected is 4359178

    1. ROW(INDIRECT("1:"&LEN(A1))) is equivalent to ROW(INDIRECT("1:"&16)) which is equivalent to ROW(INDIRECT("1:16"))

    When the above is used in an array formula, which also includes a SUMPRODUCT function, it acts like an array of values from 1 to 16, ie

    {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}

    2. MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) is equivalent to MID("fdsff43t59r178dq",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1) which results in the array {"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}

    3. From the above, ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1) would equate to ISNUMBER({"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}/1).

    If you use any mathematical operation on a text that is numeric in nature, it will return another numeric value, and the other would return an error. So the above formula would essentially give an array {FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}

    4. Now comes LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))

    For this you need to understand how the LARGE function works (I'll skip that).

    The above equation would equate to

    LARGE({FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}
    *{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as

    LARGE({0, 0, 0, 0, 0, 6, 7, 0, 9, 10, 0, 12, 13, 14, 0, 0},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as

    {14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0}

    5. Now comes the MID formula

    MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)

    You all know how the MID formula works. MID(TextToLookAt,CharacterPositionToStartFrom,Leng thOfCharacters)

    The character position to start from should not be less than 1. Our array above, ie, {14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0} has quite a few zeroes in it. To nullify a probable error in the formula, we add a 0 (zero) to the beginning of the original text, and we offset the array above by 1 (one). There's another reason why we add a zero to the beginning, which will be clear as we continue with the explanation.

    So the MID formula would equate to

    MID("0fdsff43t59r178dq", {15,14,13,11,10,8,7,1,1,1,1,1,1,1,1,1}, 1) would equate to {"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}

    6. Now comes the really interesting part of the equation.

    You know what x^y, ie, X powered by Y, means, so this should be straight forwards

    MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1) would equate to

    {"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}-1 which equates to

    {"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15} which equates to

    {8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}

    7. SUMPRODUCT({8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}) would give us the result 4359178
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Jan 2012
    Posts
    2
    Rep Power
    0

    Help needed to understand usage of '^' and '0&A' in the below formula.

    Can you please explain me use of having '0 & A' and also '^' in this below formula.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    0 is added just to ensure that there are no errors. Everything else can be found out by evaluating the formula. Post back if that's still not clear.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    A link that shows hows to extract numbers that are placed together. Ex. ABC123DEF

    Extracting numbers from alphanumeric text
    Here is another formula that does this...

    =LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

    I think my formula has more characters in it, but it should still be faster than the code at that link because it has three less function calls and it is not array-entered (just press the ENTER key to commit it).
    Last edited by Rick Rothstein; 05-23-2012 at 07:17 PM.

  7. #7
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13
    Hi Rich

    =LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

    this formula is not extracting all digits from alphanumeric value

    My Input was a0-112455jkjk223
    and result is coming 0

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    =LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

    this formula is not extracting all digits from alphanumeric value

    My Input was a0-112455jkjk223
    and result is coming 0
    My formula was not meant as a replacement for this article's original formula. If you look at the section I quoted, you will see I was responding to this statement from the second message in this thread...

    "A link that shows how to extract numbers that are placed together. Ex. ABC123DE
    http://www.familycomputerclub.com/ex...eric-text.html"

    If the digits for the number are all placed together, my formula returns them... if they are not all placed together, then my formula returns the first complete number it comes to. For example, if the text were ab123cd45efg678hijk, then my formula would return 123... the first complete number in the string. For the example you posted, the 0 is the first complete number (it is followed by a dash, a non-digit), so it returns that value.
    Last edited by Rick Rothstein; 03-06-2012 at 10:34 PM.

  9. #9
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Hi Guys

    Thanks for all the input, much appreciated

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    By the way, for a more lazy but shorter code to the original post, one can use this for let's say, text that don't have more than 25 characters

    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25)) +1,1)*10^ROW($1:$25)/10)

    Picked it from the link posted here

    How to separate numbers and words | Chandoo.org Excel Forums - Become Awesome in Excel
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 2
    Last Post: 03-21-2013, 08:51 PM
  2. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  3. Extract numbers from alphanumeric values
    By tushar.tarafdar in forum Excel Help
    Replies: 3
    Last Post: 09-20-2012, 10:16 PM
  4. Count Alpha In AlphaNumeric Text
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 07-19-2011, 02:32 AM
  5. Remove Zero / Zeroes From Alphanumeric Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-04-2011, 01:16 AM

Tags for this Thread

Posting Permissions

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