VBA: getting part of string

I have the following string..it always varies in length.. Up to the ":" is consistant and the period after each line.  How to extract number out of string for each line...thanks..

Have several lines.  The below is an example.  

This is my first line  :      234.
This is my second line   :      100.
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Sorry, misunderstood.  Add the UDF below, and then use this expression to return the numbers in a
zero based array:

Dim arr As Variant

arr = RegExpFind(SomeVariable, "\d+")

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
            Pos = CLng(Pos)
        End If
    End If
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            RegExpFind = Answer
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    ' If there are no matches, return empty string
        RegExpFind = ""
    End If
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
End Function

Open in new window

Patrick MatthewsCommented:
Debug.Print Trim(Split(SomeVariable, ":")(1))
TechMonsterAuthor Commented:
It almost works.  i see the number with the period. but also the following line.
So I get this

This is my second line  
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Jaime OlivaresSoftware ArchitectCommented:
For VBA I would use:
Dim sample as String
Dim value as String
Dim colon as Integer

colon = Instr(, sample, ":")
sample = "This is my first line  :      234."
value = val(Mid(sample, colon+1, Instr(colon, sample, ".") - colon))
Based on matthewspatrick code you can do:

Cint(Trim(Split("This is my first line  :      234.", ":")(1)))

Patrick MatthewsCommented:

Just to confirm, are they two separate strings, or is it one string with multiple lines?


Jaime OlivaresSoftware ArchitectCommented:
Oops, in my sample code, there is an error in line ordering, should be:

Dim sample as String
Dim value as String
Dim colon as Integer

sample = "This is my first line  :      234."
colon = Instr(, sample, ":")
value = val(Mid(sample, colon+1, Instr(colon, sample, ".") - colon))

the real job is made in the last 2 lines
TechMonsterAuthor Commented:
One string.  
TechMonsterAuthor Commented:
I was kinda prone to the original idea.
I think the method would work if the starting could be ":" and the end point is "."

So just need to get data between the : and the .  

Patrick MatthewsCommented:
TechMonster said:
>>I was kinda prone to the original idea.

Did you try my 2nd attempt, with the UDF?
TechMonsterAuthor Commented:
Not yet..working on it...
TechMonsterAuthor Commented:
Ok,,,I've got it... I am just repeating this for each line....changing the the inStr each time..


idx = InStr(s, "This is my first line    :")
    result = Mid(s, idx, l - idx)
    colPos = (InStr(result, ":") + 1)
    perPos = InStr(result, ".")
    dataLen = (perPos - colPos)
    result= Mid(result, colPos, dataLen)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.