?
Solved

Using instrRev and getting the second to last instance of a string

Posted on 2007-07-29
7
Medium Priority
?
242 Views
Last Modified: 2008-02-01
I am looking for code to find the next to last instance of a string.

For example, if the string I am reading (from a text file) is "Miami, FL, United States" I want to pull "FL" from that string.

The string I am reading is not always in this format (it's someone else's file) from the start of the string. However, I always has the state then united states, so I basicaly want to 'read backwards' from to the second to last comma.

I know I can use InstrRev but not how to grab the second to last, basically
0
Comment
Question by:scbdpm
  • 3
  • 2
  • 2
7 Comments
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 750 total points
ID: 19589143
"The string I am reading is not always in this format (it's someone else's file) from the start of the string. However, I always has the state then united states, so I basicaly want to 'read backwards' from to the second to last comma."

Use Split() like this:

    Dim strIn As String
    strIn = "a,b,c,d,e,Miami, FL, United States"
   
    Dim fields() As String
    fields = Split(strIn, ",")
   
    Dim state As String
    state = fields(UBound(fields) - 1)
    Debug.Print state
0
 
LVL 3

Expert Comment

by:lorelogic
ID: 19589153
Use the Split Function

Function GetStateFromString( addressString As String) As String
  Dim addressArray () As String
   addressArray () =  Split(addressString, ",")
   return = addressArray(addressArray().Length - 2)
End Function
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 19589158
What language is that supposed to be lorelogic?

Looks like a cross between VB6 and VB.Net...but it has incorrect syntax for either language!

=\
0
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.

 
LVL 3

Expert Comment

by:lorelogic
ID: 19589174
Sorry, probably a mix of that and c#. Here it is for VB.NET:

 Function GetStateFromString(ByVal addressString As String) As String
        Dim addressArray() As String
        addressArray = Split(addressString, ",")
        Return addressArray(addressArray.Length - 2)
    End Function
0
 

Author Comment

by:scbdpm
ID: 19589180
can you give it for VB6?
0
 

Author Comment

by:scbdpm
ID: 19589183
actually looking over these two options, neither will work.

the reason being is that sometimes the state is in the third 'place' in the string, sometimes it is the fourth.

however, state is always in the next to last, that's why I was looking for something using the end and working backward, sorry if I wasn't clear in my original question
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 19589195
"however, state is always in the next to last, that's why I was looking for something using the end and working backward, sorry if I wasn't clear in my original question"

That is precisely what my code does!

Try it out...I purposely put some extra data in the input value just to show that it was getting the second to last "field":

    strIn = "a,b,c,d,e,Miami, FL, United States"

Change it up and change the number of data fields before and see what happens...  =)

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

749 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