Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Problem: In cell A1-A100, I have the following types of data: "ABC 1234-5678", or "DEF 9012-3456 ", etc. In other words, there is text entered as the first, say, 1-25 characters of a cell, then anywhere from 1 to 50 spaces, then 4 numbers, then a hyphen, then 4 more numbers, then anywhere from 0 to 50 additional spaces. What I would like to do is extract from cells A1:A100 all of the numbers in their original format (i. e., including the hyphen, "1234-5678", "9012-3456") into cells B1:B100. Any ideas? Thanks in advance.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use this UDF:
    Code:
    Function GetNumbers(strInput As String) As String
        Dim RegExp As Object, match, matches
        Dim strPattern As String
        Set RegExp = CreateObject("vbscript.regexp")
        strPattern = "\d{4}-\d{4}"
        With RegExp
            .Global = True
            .IgnoreCase = True
            .Pattern = strPattern
             Set matches = .Execute(strInput)
            If matches.Count > 0 Then GetNumbers = matches(0)
        End With
    End Function
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Rory. Now, how do I get it to work?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This post has been cross posted in Another forum where another solution has been offered.
    Regards
    John



  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    This post has been cross posted in Another forum where another solution has been offered.
    Yes I did post on Eileen's Lounge where my friend Hans resides now. Although a solution has been offered, it does not do the trick. All I would like to hear from Rory, or anyone for that matter, is how I can possibly get his UDF to work. Plain and simple! No hidden agendas here!

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by jlkirk View Post
    Yes I did post on Eileen's Lounge where my friend Hans resides now. Although a solution has been offered, it does not do the trick. All I would like to hear from Rory, or anyone for that matter, is how I can possibly get his UDF to work. Plain and simple! No hidden agendas here!
    Does it need to be a UDF?

    You could use a formula such as
    =right(trim(A1),9)

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Gfamily. That appears to work.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you needed to copy that code into a module, then just use it as you would any other formula:
    =GetNumbers(A1)
    for example.
    I confess, I missed that the last characters would only be spaces not other text!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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