Closed Thread
Results 1 to 12 of 12

Extract number from alphanumeric string

  1. #1
    Registered User
    Join Date
    01-20-2008
    Posts
    4

    Extract number from alphanumeric string

    Hi all,

    What is the formula to be used to extract number from a alphanumeric string located at different row?

    example:
    test123 test128 test131
    test124 test129 test132
    test125 test130 test133
    test126 test131 test134
    test127 test132 test135
    test128 test133 test136

    result:
    123 128 131
    124 129 132
    125 130 133
    126 131 134
    127 132 135
    128 133 136

    Thanks & Best Regards,
    Chiwai
    Last edited by VBA Noob; 01-20-2008 at 04:37 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,026
    From your example, find and replace "test" with nothing.

  3. #3
    Registered User
    Join Date
    01-20-2008
    Posts
    4

    Extract number from alphanumeric string

    Yes... thanks many.
    But, what is the formula to extract the number without corrupt the original data, can the result return at some where else?


    an example:
    J1 = 251 chances
    J2 = 1250chances
    J3 = red 1000chances
    J4 = Blue30chances
    J5 = White222222


    Result display:
    K1 = 251
    K2 = 1250
    K3 = 1000
    K4 = 30
    K5 = 222222

    Thanks & Best Regards,
    Chiwai

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try, In K1:

    Please Login or Register  to view this content.
    copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    01-20-2008
    Posts
    4

    Smile Extract number from alphanumeric string

    Yes...Thanks alot.
    Really appreciate it

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    An alternative, less typing

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


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  7. #7
    Registered User
    Join Date
    01-20-2008
    Posts
    4

    Smile Extract number from alphanumeric string

    Hi all,

    How do i unscramble this 2 formula functions?

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


    =REPLACE(LEFT(J1,LOOKUP(10,MID(J1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(J1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0


    Thanks & Best Regards,
    ChiWai

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi Chiwai,

    How do i unscramble this 2 formula functions?

    =LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0 123456789")),ROW($1:$10000))))
    I'm not an MVP so I can honestly say I don't know how it works, other than is does. I've obviously come across this formula, may here on this forum, I didn't make a note at the time.

    But here's another function that does exactly the same with an explanation from Ashish Mathur, a Microsoft MVP (Most Valuable Professional)....hope this helps.

    May be I should read it too!

    http://office.microsoft.com/en-us/ex...549011033.aspx

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Also, if you select the cell with the formula and go to Tools|Formula Auditing|Evaluate Formula... you can step through the formula as it evaluates it to see what happens and when....this should help understand how the formula(s) work.

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674
    Quote Originally Posted by laichiwai
    Hi all,

    How do i unscramble this 2 formula functions?

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


    =REPLACE(LEFT(J1,LOOKUP(10,MID(J1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(J1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0


    Thanks & Best Regards,
    ChiWai
    Here's how the first one works,

    Firstly, normally formulas "start" somewhere in the middle when you want to decipher them!

    This part

    SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0123456789")

    finds the position of the first of each digit in the string in J1, J1 is concatenated with 0123456789 so that the SEARCH function doesn't return an error [note: if you use 1234567890 with zero at the end you can get rid of the quotes]. So imagine J1 contains the string "Blue30chances"

    Then this returns the array

    {6,15,16,5,18,19,20,21,22,23}

    6 is the position of the zero, 5 the position of the 3 and the other numbers are bigger than the string length because they represent the positions of 1,2,4 etc in the string "Blue30chances0123456789"

    MIN now takes the smallest number in that array, i.e. 5, representing the position of the first digit in the string.

    Now MID function comes into play as

    MID(J1,5,ROW($1:$10000))

    [I've replaced the SEARCH function with its result, i.e. 5]

    Using ROW($1:$10000) is possibly overkill but it allows for the string to be up to 10000 characters long [note: this isn't particulary "robust", inserting rows in your spreadsheet could cause the formula to fail]

    ROW($1:$10000) generates an array of 10000 numbers, every number from 1 to 10000, effectively allowing the MID function to generate up to 10000 substrings, starting at the first digit, limited by the length of J1, so in our example MID returns an array a bit like this

    {"3";"30";"30c";"30ch";"30cha";"30chan";"30chanc";"30chance";"30chances";"30chances";"30chances";...etc.}

    MID function has "0" concatenated to the front of it, probably to avoid an error [and return 0] if J1 is blank

    Now the -- converts the above array to values, text can't convert to values so you get errors like this

    {3;30;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE...etc.}

    Note that the value we want [30] is the last numeric value in the array.

    When you use LOOKUP with a lookup value as an infeasibly large number, e.g. 99^99 you extract the last numeric value from the array, so formula returns 30.

    Given my comments above I'd probably modify to

    =LOOKUP(99^99,--(0&MID(J1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J1&1234567890)),ROW(INDIRECT("1:"&LEN(J1)+1)))))

  11. #11
    Registered User
    Join Date
    01-12-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Extract number from alphanumeric string

    Been struggling with this for a few hours, I'm a beginner

    Want to extract isbn number from column A strings and dump them into column B. I'm getting the feeling this will take multiple formulas to get proper final output?

    A1 = Introduction to Environmental Geology ISBN: 0-13-144764-5
    A2 = ISBN: 0-534-62361-1 Good condition
    A3 = ISBN: 080537146X

    B1 = 0131447645
    B2 = 0534623611
    B3 = 080537146X

    Thank you

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,026

    Re: Extract number from alphanumeric string

    Welcome to the forum, Jason.

    Please tale a few minutes to read the forum rules, and then start your own thread.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

Closed 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