Extract number from the right of the string
Results 1 to 9 of 9

Thread: Extract number from the right of the string
Thanks Thanks: 0 Likes Likes: 0

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

    Default Extract number from the right of the string

    HI there,

    I wonder if there is any way that we can extract number from the right of a string, for example:

    ABC123 => 123
    ABCD$DE%34 => 34

    Thank you very much!

  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: Extract number from the right of the string

    Hi.

    See here for some techniques:

    Extracting numbers from a string 2: Consecutive numbers at end | EXCELXOR

    Let me know if you need anything explaining.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

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

    Default Re: Extract number from the right of the string

    thank you very much!

  4. #4
    New Member
    Join Date
    Sep 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract number from the right of the string

    Quote Originally Posted by XOR LX View Post
    Hi.

    See here for some techniques:

    Extracting numbers from a string 2: Consecutive numbers at end | EXCELXOR

    Let me know if you need anything explaining.

    Regards
    In a simpler case, If the delimiter is defined, for example the "$", is there an easy way to extract the number?

    For instance:

    =Tickmarks!$A$3 => 3
    '=Tickmarks!$ABC$32323 =>32323
    =Tickmarks!A17 => 17

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,969
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract number from the right of the string

    Quote Originally Posted by sebastriantran View Post
    In a simpler case, If the delimiter is defined, for example the "$", is there an easy way to extract the number?

    For instance:

    =Tickmarks!$A$3 => 3
    '=Tickmarks!$ABC$32323 =>32323
    =Tickmarks!A17 => 17
    Where is the $ sign in that last one?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Sep 2014
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract number from the right of the string

    Quote Originally Posted by Rick Rothstein View Post
    Where is the $ sign in that last one?
    Yes, that's my typo, it should be $ sign instead of ! sign. I use the Instr and Left function to extract the number, but I wonder if there is any easier way to do it :D

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,969
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract number from the right of the string

    Quote Originally Posted by sebastriantran View Post
    Yes, that's my typo, it should be $ sign instead of ! sign. I use the Instr and Left function to extract the number, but I wonder if there is any easier way to do it :D
    Oh, this is a VB question. Okay, assuming cell A1 for example purposes...

    Code:
     Number = Mid(Range("A1").Value, InStrRev(Range("A1").Value, "$") + 1)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  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: Extract number from the right of the string

    Ah, sorry. Didn't realise this was a VBA question!

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,969
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract number from the right of the string

    Quote Originally Posted by XOR LX View Post
    Ah, sorry. Didn't realise this was a VBA question!
    Until I saw the InStr reference in Message #6, neither did I.
    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
  •