Extract specific text from a string

Topic

New Reply

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

Reply | Quote

Replies

Is code acceptable?

Reply | Quote

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.

Reply | Quote

Yes – if there’s a specific list, simple formulas should work.

Reply | Quote

Can you give an assist with the formula please?
Thanks

Reply | Quote

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)) = "A" And UCase(Mid(sTemp, 2, 1)) = "0" And Mid(sTemp, 3, 1) = "0" And Mid(sTemp, 4, 1) = "0" And Mid(sTemp, 5, 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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Here is a sample. Don’t understand this at all — one record works, the others don’t.

Reply | Quote

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.

Reply | Quote

other than the dual columns I only get the error if none of the items are not found in the string…

Steve

Reply | Quote

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.

Reply | Quote

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.

That will happen if you have blank cells included in the lookup list range. Otherwise you should see an error value, which would be more obvious.

Reply | Quote

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.

Reply | Quote

Rory, thanks, had the formula starting at F1 instead of F2. Changed and now see the error value. I think I’ll go with your formula; it finds the matches and verifies with the valid list at the same time. Can you explain what the 1E+100 means in your formula?

Reply | Quote

It’s a really big number (10 to the power of 100) guaranteed to be bigger than any number SEARCH might return (otherwise LOOKUP could return a #N/A error even with a found item).

Reply | Quote

Thanks so much everyone. You’ve saved me at least three days of work on just this one project alone. Really appreciate your willingness to help.
Meleia

Reply | Quote

I was wrong. I tested it, it semed to work, I posted it. I was going over again with different scenarios and found the errors which lead me astray…

If the list item is in the same row as the output and you forget to hit ctrl-shift-enter, it works (so yes, it does not work…).

I will put a note in my original post, so I don’t lead others astray…

Steve

Reply | Quote

Hey Steve,
I’m not sure what you just said, but it was said with eloquence! I am still going to try the code you sent originally (when I have a little more time) and see what I can learn. I appreciate all your help.
Meleia

Reply | Quote

The UDF code works.
It is the formula I posted that was a bust.

Steve

Reply | Quote