Solved

VBA: getting part of string

Posted on 2008-10-09
12
1,202 Views
Last Modified: 2013-11-25
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.
0
Comment
Question by:TechMonster
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 92

Expert Comment

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

Author Comment

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

 234.
This is my second line  
0
 
LVL 92

Accepted Solution

by:
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: 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
        Else
            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)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            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
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
    
End Function

Open in new window

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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))
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 22680985
Based on matthewspatrick code you can do:

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


Regards
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22681052
TechMonster,

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

Regards,

Patrick
0
 
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
0
 

Author Comment

by:TechMonster
ID: 22681463
One string.  
0
 

Author Comment

by:TechMonster
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 .  

Thanks.
 
0
 
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?
0
 

Author Comment

by:TechMonster
ID: 22681738
Not yet..working on it...
0
 

Author Comment

by:TechMonster
ID: 22682058
Ok,,,I've got it... I am just repeating this for each line....changing the the inStr each time..

Thanks.

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)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now