Occurance Number Within a String

J

JohnnyBGood

Hi,

How can I return the occurance number of the comma just before a
specific word, say for this example, before "Red"

Green, Blue, Purple, Red, Yellow, Orange

So I should get the result: 3

TIA.
 
C

Chip Pearson

Try something like

Dim S As String
Dim T As String
Dim L As Long
Dim Pos As String
S = "Green, Blue, Purple, Red, Yellow, Orange"
Pos = InStr(1, S, "Red")
T = Left(S, Pos)
L = Len(T) - Len(Replace(T, ",", ""))
Debug.Print L


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"JohnnyBGood"
in message
news:[email protected]...
 
J

JohnnyBGood

Thanks a lot Chip,

I guess there is no easy way to do that using strictly Excel formulas?

By the way, If I use your code, the word won't necessarily be always
Red. How would that be change to reference a cell. Sorry, not too VBA
savvy, that's why I prefer the Excel formula if you have one.

Thanks again.
 
C

Chip Pearson

You can do it with a formula. Assuming A1 contains

Green, Blue, Purple, Red, Yellow, Orange
and A2 contains
Red

use the following formula:

=LEN(LEFT(A1,FIND(A2,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(A2,A1)-1),",",""))

There is no error checking here. It is assumed that the string in
A2 is in fact in A1.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JohnnyBGood"
in message
news:[email protected]...
 
J

JohnnyBGood

Hi again Chip,

I figured it out. Combining my knowledge in Excel and your coding, I
was able to get the same result using 1 Excel formula.

Thanks for the help.

=LEN(LEFT(F37,SEARCH("Red",F37)-2))-LEN(SUBSTITUTE(LEFT(F37,SEARCH("Red",F37)-2),",",""))

Where F37 contains the string, and, of course, I can sub "Red" string
with a cell reference.
 
Top