Extract Text From String

Hi,

I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.

DDR_WF602_____02_00_001_FLANGE_WR34_4_HOLE_PLAIN_CLEARANCE

I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.

I can get rid of the first 4 characters using

A = Len(PartCode)
        A = A - 4
        PartCode= Right(PartCode, A)

But am having a mind blank and unsuccessful searches on how to remove the rest of the string

Any hints would be appreciated,

Thanks

Just reasised a further requirement:

If the section of string extracted ends in a letter not a digit, I would like the string minus the final digit returned. For example from the following string I would like to get WA603, not WA603L

DDR_WA603L____01_00_001_CLAMP_WR34_E_PLANE_DOUBLE_LOWER

The way I am thinking of doing this, is to isolate the final character, check if it is numeric and if not shorten the string until the final character is numeric.

Hey,

Good afternoon to you too.

Thanks for the quick reply, was writing my other post while you replied.

The code works great, thanks.

There was no reference to where the string is as it's location is variable within the sheet, I'm working from an imported text file so the stuff is all over the place, but the program works fine to locate the string.

Think I can do the second part of my post myself, so thanks again for the help.

That's excellent, thanks

I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.

Dim myCode As String, m As Object
myCode = "DDR_WA603L____01_00_001_CLAMP_WR34_E_PLANE_DOUBLE_ LOWER"
With CreateObject("VBScript.RegExp")
    .Pattern = "_[A-Za-z0-9]{5}_"
    If .test Then
        .Pattern = "[A-Z]_$"
            MsgBox Mid$(.replace(.execute(myCode)(0),""),2,5)
    End If
End With

Hey Jindon.

I had a look at your code, and it gets stuck at the

If .test Then

line, saying wrong number of arguments. Also the length of the string I want to return is not necessarily 5 characters.

Domonic's code is working fine so I am going to stick with that for now. Thanks anyway, it's good to see an example of how you would do it with a RegExp function as they currently confuse me an awful lot and yet seem very clever and useful if I could get my head round them