Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    Extract specific text from a string

    Hi All,

    I have data which includes a specific text string in each record; letter-letter-number-number-number-number (NM5762 for example). This string may be anywhere in the record (see attached example). I would like to know if there is a way to extract the string from the record.

    Secondly, in another column the data includes a number; 10, 20, 30 etc. I need these numbers to be changed to four characters i.e. 0010, 0020, etc. Not just a format mask but an actual change to 4 characters.

    Any help with these is appreciated and since you all have done such a wonderful job for me in the past, I expect great things here!

    Thanks
    Meleia
    Attached Files Attached Files

  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
    Is code acceptable?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Yes, code would be acceptable although I have not done anything with VBA before.

    Just had a thought that may help -- I have a list of the letter-number combinations that will exist in the strings if it would help to compare one to the other.

  4. #4
    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
    Yes - if there's a specific list, simple formulas should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Can you give an assist with the formula please?
    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    For the numbers you can add in B2 a formula like:
    =TEXT(A2,"0000")
    For the extraction, I created a UDF, which you can add to a module (you can change the name if desired):
    Code:
    Option Explicit
    Function FindMe(str As String)
      Dim sTemp As String
      Dim x As Integer
      Dim i As Integer
      Dim vParse As Variant
      
      'Parse string
      vParse = Split(str, " ")
      
      'check Words
      For x = LBound(vParse) To UBound(vParse)
        sTemp = vParse(x)
        If Mid(sTemp, 1, 1) = "#" Then 'ignore the #
          sTemp = Mid(sTemp, 2)
        End If
        If Len(sTemp) = 6 Then 'length is right
          If UCase(Mid(sTemp, 1, 1)) >= "A" And UCase(Mid(sTemp, 1, 1)) <= "Z" _
            And UCase(Mid(sTemp, 2, 1)) >= "A" And UCase(Mid(sTemp, 2, 1)) <= "Z" _
            And Mid(sTemp, 3, 1) >= "0" And Mid(sTemp, 3, 1) <= "9" _
            And Mid(sTemp, 4, 1) >= "0" And Mid(sTemp, 4, 1) <= "9" _
            And Mid(sTemp, 5, 1) >= "0" And Mid(sTemp, 5, 1) <= "9" _
            And Mid(sTemp, 6, 1) >= "0" And Mid(sTemp, 6, 1) <= "9" Then
            'meets criteria
            FindMe = sTemp
            Exit Function 'stop testing
          End If
        End If
      Next
    End Function
    Then in E2 you can just enter the formula:
    =findme(D2)

    It first parse the words at the spaces. The it looks at each word [and ignores if the first letter is hashmark(#)], then it loops through and checks that any string of 6 chars has the pattern (letter,letter, number, number,number,number) and then gets that.

    Steve

    PS
    IGNORE THIS If you have a complete list and your list is in K2:K100 (for example), you could use the array formula (confirm ctrl-shift-enter):
    =MID(D2,FIND($K$2:$K$100,D2),6)
    IGNORE THIS it is wrong


    PPS. The UDf will give a 0 if no value is found. If you want something else you can add a line after the dim statements like:
    FindMe = CVErr(xlErrNA) '#NA error with not found
    or
    FindMe = "" 'gives a null string if not found
    Last edited by sdckapr; 2013-10-03 at 12:57. Reason: Added PS with formula, PPS with code addon

  7. #7
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    I'll give this a try. Thanks. You mentioned that a simple formula might work if I have a list to compare the text strings to. Can you give me an example of the formula?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I added a formula to the PS (which you probably missed by reading before I added the PS):
    IGNORE THIS If you have a complete list and your list is in K2:K100 (for example), you could use the array formula (confirm ctrl-shift-enter):
    =MID(D2,FIND($K$2:$K$100,D2),6)
    IGNORE THIS it is wrong

    I also added a code "improvement" in the PPS...

    Steve
    Last edited by sdckapr; 2013-10-03 at 12:57.

  9. #9
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    I am trying the formula above and getting a #Value! error. Any idea why? All data is formatted as general, the range is correct for the list and have entered as ctrl-shift-enter array.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    No idea why. It worked in the example I created...

    Is your complete list a multi-columned range? That is the only way I could create the error. The list must be a single column.

    If that is not the issue, could you post a truncated sample copy demonstrating the problem?

    Steve

  11. #11
    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
    I get that error too if the first item in the list isn't a match. For the same list range, assuming no blank cells in that range, you can use:
    =MID(D2,LOOKUP(1E+100,SEARCH($I$1:$I$7,D2)),6)
    which doesn't need to be array entered.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Here is a sample. Don't understand this at all -- one record works, the others don't.
    Attached Files Attached Files

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    other than the dual columns I only get the error if none of the items are not found in the string...

    Steve

  14. #14
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    I have the list of data and the list of valid combinations. I'm not following what you mean by "dual columns". Can you tell why one record would work in the Book 3 example but the others don't?

    I tried Rory's formula above and it works. If a value isn't found in the compare list it defaults to the first 6 characters in the data column. That's Ok; it shows where there is no match.

  15. #15
    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
    Steve,
    That's very interesting - I actually don't see how your formula can work for anything other than a match on the first item in the list. If the first item doesn't match, FIND returns an error and so does the formula.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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