2

For example, I have this string that reads "IRS150Sup2500Vup". It could also be "IRS250Sdown1250Vdown".

I am looking to extract the number between the two S. Hence for the first case, it will be 150 and second case, it is 250. The numbers are not always 3 digits. It could vary.

What I have tried:

Dim pos As Integer
Dim pos1 As Integer

pos = InStr("IRS150Sup2500Vup", "S")
pos1 = InStrRev("IRS250Sdown1250Vdown","S")

After this, I am stuck how to get the number out.

Need some guidance on how to do this.

lakshmen
  • 28,346
  • 66
  • 178
  • 276
  • 1
    There is a Regex COM component you can use in VBA to handle Regex. Please see this post : http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops. You can then use the Regex with groups to extract the data easily. – gretro Jan 13 '15 at 02:06

3 Answers3

4

As i suggested here, the simplest way is to use Regex.

Sub Test()
Dim r As VBScript_RegExp_55.RegExp
Dim sPattern As String, myString As String
Dim mc As VBScript_RegExp_55.MatchCollection, m As VBScript_RegExp_55.Match

myString = "IRS150Sup2500Vup"
sPattern = "\d+" 'searches for numbers 
Set r = New VBScript_RegExp_55.RegExp
r.Pattern = sPattern

Set mc = r.Execute(myString)
For Each m In mc ' Iterate Matches collection.
    MsgBox "number: '" & m.Value & "' founded at: " & m.FirstIndex & " length: " & m.Length
Next

End Sub
Community
  • 1
  • 1
Maciej Los
  • 8,468
  • 1
  • 20
  • 35
3

Here is an option:

Public Sub Test4()

    Dim pos As Integer
    Dim pos1 As Integer
    Dim strOrig As String
    Dim strString As String

    strOrig = "IRS150Sup2500Vup"

    pos = InStr(1, strOrig, "S") + 1
    pos1 = InStr(pos, strOrig, "S")
    strString = Mid(strOrig, pos, pos1 - pos)

    MsgBox strString

End Sub
E. A. Bagby
  • 824
  • 9
  • 24
-1

Try using this function:

pos = Mid("IRS150Sup2500Vup", 4, 6)
Dane I
  • 742
  • 9
  • 22