VBA: getting part of string

Posted on 2008-10-09
Last Modified: 2013-11-25
I have the following 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.
Question by:TechMonster
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22680715
Debug.Print Trim(Split(SomeVariable, ":")(1))

Author Comment

ID: 22680826
It almost works.  i see the number with the period. but also the following line.
So I get this

This is my second line  
LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 22680867
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: 
    ' 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 55

Expert Comment

by:Jaime Olivares
ID: 22680869
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))

Expert Comment

ID: 22680985
Based on matthewspatrick code you can do:

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

LVL 92

Expert Comment

by:Patrick Matthews
ID: 22681052

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


LVL 55

Expert Comment

by:Jaime Olivares
ID: 22681170
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

Author Comment

ID: 22681463
One string.  

Author Comment

ID: 22681547
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 .  

LVL 92

Expert Comment

by:Patrick Matthews
ID: 22681713
TechMonster said:
>>I was kinda prone to the original idea.

Did you try my 2nd attempt, with the UDF?

Author Comment

ID: 22681738
Not yet..working on it...

Author Comment

ID: 22682058
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)

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Added a column screws up code 5 69
regular expression for 10 digit number 7 97
Need some help with regex 3 53
VBA: loop recent folder and copy txt file. 8 51
Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question