Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA: getting part of string

Posted on 2008-10-09
12
Medium Priority
?
1,234 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
[X]
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
12 Comments
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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
Industry Leaders: 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))
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 93

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 93

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

618 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