+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 26

extracting numbers from alphanumeric strings

  1. #1
    Registered User
    Join Date
    08-16-2008
    Location
    NY
    MS-Off Ver
    2007
    Posts
    3

    Talking extracting numbers from alphanumeric strings

    First time in this forum. Hi all. I have a challenging question, is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

    DATA output should be
    asd67,h876 --------> 67876
    2,3,ujdj5&34 -------> 23534
    909k86m34 --------> 9098634

    Hope this makes sense?

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321
    for up to 16 characters this will work
    wait for it
    its looooong
    surely there must be a beter way(with functions) ? in fact i know it can be done by vba, but i just like messing with functions
    Please Login or Register  to view this content.

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,882

    Challenging: extracting numbers from alphanumeric strings

    Try this

    A1: abc123def456ghi789

    First, create a Named Formula
    Names in Workbook: Seq
    Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

    This ARRAY FORMULA removes ALL non-numerics from a string
    B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

    In the example, the formula returns: 123456789
    Ron
    Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,137
    Ron, that is very clever.

    It appears that seq can be defined as =ROW($1:$255). Is there a disadvantage to doing so?
    Last edited by shg; 08-16-2008 at 12:46 PM.

  5. #5
    Registered User
    Join Date
    08-16-2008
    Location
    NY
    MS-Off Ver
    2007
    Posts
    3
    Thanks for the replies. Martindwilson you wasn't kidding about how loooong that formulae was, but it worked. Thanks. Ron, not quite sure on how to create Named formulae. A bit confused on that part, as for the rest, I'm quite o.k. Can you provide me with some guidance as to how this is done. The formula seems very interesting and I would like to see it applied. Much appreciated.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,137
    Insert > Name > Define, seq Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

  7. #7
    Registered User
    Join Date
    08-16-2008
    Location
    NY
    MS-Off Ver
    2007
    Posts
    3
    OK! Got it! Thanks Shg. I tried it and it worked like a charm. Thanks again to all for the great help and very interesting manner of obtaining the desired result. Truly surpassed my expectations.

  8. #8
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,882

    Alternative range ref

    Quote Originally Posted by shg
    Ron, that is very clever.

    It appears that seq can be defined as =ROW($1:$255). Is there a disadvantage to doing so?
    Thanks, shg...The only potential hazard to using ROW($1:$255) is the
    insertion of rows at Row_1...which would shift the reference to ROW($2:$256)...etc

    I also played with ROW($1:$65536)...the end result....
    Excel unceremiously exits without so much as a warning. (no idea why)

    I'd stick with the formula as posted.

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

    My first thought was ROW(INDIRECT("1:255")), which is invariant, but the INDIRECT is volatile.

  10. #10
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Hi guys,


    Nice formula Ron. Here is another formula that it will work. But it uses INDIRECT and we all know it is volatile. It seems to calculate faster maybe because it uses less functions.



    Please Login or Register  to view this content.
    The formula is an-array need to hold down;

    Ctrl,Shift,Enter

    Hope it helps!
    Last edited by vane0326; 08-17-2008 at 12:44 AM.

  11. #11
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,882

    Challenging: extracting numbers from alphanumeric strings

    How about spitting the difference between Harlan Grove's formula (the one I posted)
    and the formula you posted....

    This variation uses a method to avoid CTRL+SHIFT+ENTER while
    still using the Seq named range....
    Please Login or Register  to view this content.
    I like that blend. It avoids the array enter AND the MMULT function
    (which is not really intuitive at all).

  12. #12
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Smile

    Quote Originally Posted by Ron Coderre
    How about spitting the difference between Harlan Grove's formula (the one I posted)
    and the formula you posted....

    This variation uses a method to avoid CTRL+SHIFT+ENTER while
    still using the Seq named range....
    Please Login or Register  to view this content.
    I like that blend. It avoids the array enter AND the MMULT function
    (which is not really intuitive at all).

    Now that is a formula I'll be using for now on. Great job Ron!! But too bad there's not a all in one formula to extract letters just using the functions that are native to excel. Thats a real challenge.

  13. #13
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Nice formula, Ron.
    //Ola

  14. #14
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252
    Quote Originally Posted by Ron Coderre View Post
    Thanks, shg...The only potential hazard to using ROW($1:$255) is the
    insertion of rows at Row_1...which would shift the reference to ROW($2:$256)...etc

    I also played with ROW($1:$65536)...the end result....
    Excel unceremiously exits without so much as a warning. (no idea why)

    I'd stick with the formula as posted.
    Ron, that's a [yet another!] tremendous formula.

    With the original Harlan Grove formula, I believe Excel crashes because of limitations with the transpose function: there is a 255 character per array element and there is a limit on the number of elements allowed (I believe this depends on the version of Excel and available memory) when using transpose.


    Out of curiosity and trying to understand the mechanics, I had a play around and I managed to remove one of the nested Index functions:
    =SUM(INDEX(MID(0&A1,INDEX(LARGE(INDEX(ISNUMBER(--MID(A1,Seq,1))*Seq,0),Seq),0)+1,1)*10^Seq/10,0))

    Changed to:
    =SUM(INDEX(MID(0&A1,LARGE(INDEX(ISNUMBER(-MID(A1,seq,1))*seq,0),seq)+1,1)*10^seq/10,0))
    Although, I've probably walked into some unforseen problems with this?


    Another variation is to use Sumproduct to remove another one of the index functions:
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(-MID(A1,seq,1))*seq,0),seq)+1,1)*10^seq/10)
    So I guess those are other options, but I have no idea which is best?


    Thank you for sharing this 'Index' technique to workaround having to CTRL+SHIFT+ENTER the formula - it's really fascinating.

    Colin

  15. #15
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,882

    extracting numbers from alphanumeric strings

    Yeah....I guess I got a little overzealous with the INDEX function.
    I would:
    1) Use a slightly edited version (see 2 below) of the last variation you posted:
    Please Login or Register  to view this content.
    but
    2) Use --MID instead of -MID
    because --MID clearly indicates that we're coercing text values to numeric.
    Using -MID, while shorter by one character, is less obvious. In
    very complex formulas that need de-bugging, it looks like a piece of
    the formula is missing or a typo.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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