Page 1 of 8 123 ... LastLast
Results 1 to 10 of 71

Thread: Extracting Multiple Numbers from String

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extracting Multiple Numbers from String

    I have thousands of fields that contain addresses and I need to extract only the numbers. The problem I am facing is that there are two numbers within the string and they begin at different times.

    Examples of the data
    1409 N 250 W
    259 West 158 Johnson Blvd
    109829 South 344 East

    What Im trying to get
    1409250
    259158
    109829344

    Thanks,
    Bolillo

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,934
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Welcome to the Board!

    Since all the numbers are not together I believe this can only be done with a user-defined function and not with excel's native functions. You can use this in a module:

    Code:
    '--------------------------------------------------------------------------------------------------------
    '***NEW FUNCTION***
    'Description: Separates Alphabetic and Numeric Data.  (i.e. Alpha 123, Alpha123)
    '             True or 1 will return alphabet, False or 0 will return numeric
    'Example:   A1 value is Rob12Schiele34
    '           =Sep(A1,1)      Returns:    RobSchiele
    '           =Sep(A1,0)      Returns:    1234
    Function Sep(txt As String, flg As Boolean) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(flg = True, "\d+", "\D+")
        .Global = True
        Sep = .Replace(txt, "")
    End With
    End Function
    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    I can put together some vba code to do this, but I'm wondering how you can use the results?

    1409250
    could be
    140 9250
    1 409250
    1409 250
    14902 50
    149025 0

    Just Asking

  4. #4
    New Member
    Join Date
    Jan 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Schielrn:

    Where do I enter that information ?

    Cbrine:

    I am comparing the information with another database and will just use it as one number.

    1409250

    Thank you both.

    Bolillo

  5. #5
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,934
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Press Alt+F11 and then press Alt, I, then M. This will insert a module. Paste this code in there and then use the formula example I have given in the description.

    Hope that helps and post back with any other questions.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Here's a formula approach...

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

    ...where A2 contains the text string of interest. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  7. #7
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    I'm not sure how much data you are working with, and I'm assuming you are doing some type of validation between the two sets of data, but you may run into the following issue.

    123N 4567 W
    12West 34567 Johnston Blvd

    They would both evaluate to 1234567, which on a straight link between the tables would cause a circadian join, and possiblity mess up your results. I would suggest you do a count of each final numbers and compare the counts. This way you would see.

    Code:
    F1             F2      F3     F4
    12334567    2       2       Equal
    Don't know if this would be an issue or not, but thought I would mention it.
    HTH
    Cal

  8. #8
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Domenic,

    Someone sent me the link to this post and I used you amazing formula. Do you think that you could read post #8 that I made? I tried to list how I understand the formula. Could you read it and see if I got that concepts correct? The post I made is here:

    http://www.mrexcel.com/forum/showthr...=1#post2193803
    Sincerely, Mike Girvin

  9. #9
    New Member
    Join Date
    Apr 2013
    Location
    Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Hi I’m a Newbie here to the board. I tried the code above and it works great. I have a little variation to the above string and my skills are limited in figuring it out. An example of my string is... in Cell G1, “C2_PU07_15.04.2013_633759 John Henry” the number I need is after the _ “633759” I currently use text to columns to break it out but I know there is an easier way. Thanks for your consideration.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,252
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extracting Multiple Numbers from String

    Quote Originally Posted by Domenic View Post
    Here's a formula approach...

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

    ...where A2 contains the text string of interest. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!
    And here is another array-entered formula that has been posted in the past by Lars-Åke Aspelin...

    =MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

    This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

    It has the following (known) limitations:

    - The input string in cell A1 must be shorter than 300 characters

    - There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)

    Maybe of no practical use, but it will also handle the following two cases correctly:

    - a "0" as the first digit in the input will be shown correctly in the output

    - an input without any digits at all will give the empty string as output (rather than 0).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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