Grabbing string values from the end of a string of an unknown length

[Mark Stephens]

Hi,

I have 3 cells containing string values:

Classification_Asset Class
Classification_Risk Classification
Classification_Geographical Breakdown

I want to tell the code to look in each cell along a row until it finds the
string value: Classification_

Whsn it find it i then want it to pass the rest of the string to another
variable, something like this:

Dim sFirstStringValue As String
Dim sSecondStringValue As String


sFirstStringValue = "Classification_"
sSecondStringValue = Everything thatcomesafterfiststringvalue

Help much appreciated,

Regards, Mark

 

[Don Guillett]

Look in the vba help index for
INSTR

 

[Mike H]

Mark,

Try this

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("A2:AA2")
For Each c In MyRange
If InStr(1, c, "Classification") = 1 Then
mystring = Mid(c, 15, Len(c))
'do something with MyString
End If
Next
End Sub

Mike

 

[Mike H]

Just realised you want to add the underscore to the search string and
eliminate iit from the 'found' string so substitute these 2 lines

If InStr(1, c, "Classification_") = 1 Then
mystring = Mid(c, 16, Len(c))

Mike

 

[Don Guillett]

or
Sub breakitup()
For Each c In Range("h1:h3")
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Next c
End Sub

 

[Mike H]

Hi,

The OP indicated and the examples given show that 'Classification' was at
the beginning of the string, your routine extracts the remaining text if the
search string appears anywhere in the cell. We will need the OP to clarify.

Mike

 

[Mark Stephens]

Hi Mike,

Thanks very much for that works like a dream, regards,

Mark

PS Don your code is very similar but slightly more elgant looking but
unfortunately I got an error : Runtime error 5 Invalid Procedure call or
argument

 

[Don Guillett]

Guess so. Actually, if few and far between then FINDNEXT would be faster
than a loop.

Sub breakwithfindNEXT()
With Worksheets("sheet7").Range("h1:h50")
Set c = .Find("Classification", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
x = InStr(c, "Classification") + 14
c.Offset(, 1) = Right(c, Len(c) - x)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub

 

[Rick Rothstein \(MVP - VB\)]

Here is a method that does not use a loop...

Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Rows(5).Find("Classification_")
On Error GoTo 0
' Not sure what you want to do with it, so here I just show it to you
If Not FoundCell Is Nothing Then MsgBox Mid(FoundCell.Value, 16)

It assumes, for example purposes, that the row you want to search is 5,
although you can substitute a fixed range instead, something like
Range("E1:E100") for the Rows(5) reference, if required. The 16 in the last
statement is the length of "Classification_", the string you want to find.

Rick

 

[Don Guillett]

Add this line as the first line in the code or use the IF mentioned by Mike.
Or, as I said in the later post, if the "hits" are far apart use the
FINDNEXT macro instead.

On Error Resume Next

 

[Tom Ogilvy]

Mark,

Since you mentiioned it, that is because Don's first code example assumes
that every cell in H1:H3 contains the word classification and that len(c) - x
doesn't result in a negative value.

If you leave out half the necessary code, the remaining code often looks
elegant.

In his second example
Set c = .Find("Classification", LookIn:=xlValues)

this line looks elegant as well, but leaves out most of the settings which
will insure that it works consistently (even if it is predominantly the
example code from the VBA FINDNEXT help example - even Microsoft was sloppy
there).

 

[Mark Stephens]

Hi Tom,

Thanks for the clarification, I am using:


Dim MyRange As Range

Dim c As Range

Dim sItemName As String





Set MyRange = Sheets("FundList").Range("C1:AA1")




For Each c In MyRange


If InStr(1, c, "Classification_") = 1 Then



pbNoItems = pbNoItems + 1

sItemName = Mid(c, 16, Len(c))



Else: sItemName = ""




End If




Next


which works well, there are just 3 cells int he top row that aren't empty ad
they all start with Classification_

In fact the prefix is unecessary for the code really but it makes the whole
thing easier to understand and is worth the overhead of stripping it out, so
thanks everyone for your help and knowledge, kind regards, Mark

 

[Mark Stephens]

Hi Rick,

I like this better since it saves me having to specify the length of the row
I want to search which makes it more scaleable and saves a bit of code
(counting the used range size to get the last entry in the row) but it only
returns the first instance how do I get it to cycle and find all the
instances of CLassification_ ?

Thanks and regards, Mark

 

[Don Guillett]

Tom is correct in that the other settings should be used if you are changing
often. See FIND for the rest of the story or record a macro using edit find
from the menu bar. I assumed you would not be using with many variables so
the defaults would suffice. The help file for findnext does refer to
FIND."Continues a search that was begun with the Find method". Findnext is
useful if you have a long column to look for a few hits.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you don’t specify values for these arguments
the next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if
you omit the arguments. To avoid problems, set these arguments explicitly
each time you use this method.

 

[Rick Rothstein \(MVP - VB\)]

Well, of course, you will need a loop to do that and, of course, things get
messier than in the simple "find it once" code I posted earlier....

Dim Index As Long
Dim FoundCell As Range
Dim FirstAddress As String
Dim Answers() As String
ReDim Answers(1 To Columns.Count)
On Error Resume Next
With Worksheets("Sheet5").Rows(5)
Set FoundCell = .Find("Classification_")
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Index = Index + 1
Answers(Index) = Mid(FoundCell.Value, 16)
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <>
FirstAddress
End If
End With
ReDim Preserve Answers(1 To Index)
On Error GoTo 0

The code above stores the values in an array named Answers. Here is a short
loop you can run after the code above to see all the results...

For Index = 1 To UBound(Answers)
Debug.Print Answers(Index)
Next

Rick

 

[Don Guillett]

I forgot to mention that when you leave Excel and come back the defaults, as
shown below in this recorded macro are restored. In your specific instance,
the defaults provided the desired results.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/7/2008 by Donald B. Guillett
'

'
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

 

[Tom Ogilvy]

Mark,

If you manually go into Edit=>Find and search for Classification_ and set it
to "match entire cell contents"

then run the macro, it will find no instances for the reason I described
earlier. If you look at Don's code that uses find, it shows how to fine all
the instances (or look at the findnext code example in vba help). But
apparently this is all going over your head. (the lesson is, when using
FIND, declare the necessary values for at least the persistent arguments).

Don has pasted in that portion of the help for easy reference.

 

[Mark Stephens]

Hi Rick, yes it does get a lot more complex, thanks everyone for a wide
variety of answers and insughts here is the final code I am using which uses
most of the inout you kindly gave me:

Sub GetItemNameAndNoItems()

Dim rTopRow As Range
Dim rClassification As Range
Dim sClassificationName As String

'These are the outputs of this sub
Dim parrsItemname() As String
Dim pbNoItems As Byte


Set rTopRow = Sheets("FundList").Range("C1:AA1")

For Each rClassification In rTopRow

If InStr(1, rClassification, "Classification_") = 1 Then

pbNoItems = pbNoItems + 1
ReDim Preserve parrsItemname(pbNoItems)
sClassificationName = Mid(rClassification, 16,
Len(rClassification))
parrsItemname(pbNoItems) = sClassificationName


End If

Next rClassification


Rick, Don, Mike, Tom, thank you all for being so generous with your time and
knowledge (as always, thanks and regards, Mark

 

[Mark Stephens]

Thanks Tom,

I think find is the best solution, it's after 1am here in Singapore so I am
going to sleep on it and re-read Don's explanation (I did look at findnext
in help but found it a bit incomprehensible, maybe I will find it makes more
sense after some sleep).

Thanks and regards, Mark

 

[Tom Ogilvy]

Don,
You seem to want to obfuscate the sound practice of specify values for
persistent parameters when using the FIND command. Is your intent to
confuse the OP, discredit the advice or are you suggesting to always close
and reopen excel whenever using code that contains the FIND command to avoid
having to specify the parameter values/never run macros unless excel has just
been opened.

In your specific instance, the defaults provided the desired results.

Not if the string is produced by a formula. there was no discussion which
said whether the strings were constants or produced by formulas. That is
another example of why it is best to specify.