Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Isolating numeric values from a character string.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Isolating numeric values from a character string.

    Thanks to Mr. Excel contributors, I found out how to use MID(A1, SEARCH("ID=",A1),8) to isolate a specific string of data from a URL.
    This already made something which seemed impossible actually work which I am grateful for.

    However, the we have new URL structures added and now the result of running the formula leaves me with both numeric, alpha and special characters.
    What I am now wondering is if there is a formula that I could run on the outcome to only provide me with the numeric values?

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    Hi,

    Could you provide us with half a dozen or so examples and your desired result(s) in each case?

    Regards

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    These would be some examples, I am looking for a numeric value which will be between 1 and six characters long, due to the two databases, I am using either SEARCH("ID=",A1),9 or SEARCH("ents/",A1),10.
    Actual Preferred
    ID=723#S 723
    ID=1009& 1009
    ents/1284/u 1284

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    Thanks. And will the desired extraction always be the only number in the string, as in your examples?

    Regards

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    Yes, in this case only ever the numbers following ID= or ents/

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    Thanks. If you're using Excel 2010 or later:

    =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&5^19)),1+MMULT(AGGREGATE({14,15},6,1/(ISNUMBER(0+MID(A2,ROW(INDIRECT("1:999")),1)))*ROW(INDIRECT("1:999")),1),{1;-1}))

    If not, let me know and I'll give you another solution.

    Regards
    Last edited by XOR LX; May 23rd, 2014 at 03:43 AM.

  7. #7
    New Member
    Join Date
    May 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    I was doing something stupid, your formula works brilliantly!
    Thank you so much...
    Last edited by Bad Rabbit; May 23rd, 2014 at 03:53 AM.

  8. #8
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Isolating numeric values from a character string.

    I assume you realised that formula was set to look at cell A2?

    Regards

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Isolating numeric values from a character string.

    A couple of other possibilities, noting ...

    a) The number is restricted to 1 to 6 digits
    b) the number follows "ID=" or "ents/" (I'm hoping following "ts/" is also sufficient?)

    .. you could try

    =AGGREGATE(14,6,--MID(A2,3+AGGREGATE(15,6,SEARCH({"ID=","ts/"},A2),1),{1,2,3,4,5,6}),1)



    If the number can be anywhere (that is, not necessarily following "ID=" etc), but still restricted to a maximum of 6 digits, then try

    =AGGREGATE(14,6,--MID(A2,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),{1,2,3,4,5,6}),1)


    Edit: XOR LX, I like the 5^19 trick. I don't remember ever seeing that before.
    Last edited by Peter_SSs; May 23rd, 2014 at 04:05 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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

    Default Re: Isolating numeric values from a character string.

    Assuming there is only ever one number to find in a cell, this formula should work no matter what version of Excel is being used...

    =LOOKUP(9E+307,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),99),ROW(INDIRECT("1:99"))))
    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

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
  •