Occurance Number Within a String
 

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.


--
JohnnyBGood
------------------------------------------------------------------------
JohnnyBGood's Profile: http://www.excelforum.com/member.php...o&userid=29783
View this thread: http://www.excelforum.com/showthread...hreadid=494903

Occurance Number Within a String
 

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"
wrote
in message
...

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.


--
JohnnyBGood
------------------------------------------------------------------------
JohnnyBGood's Profile:
http://www.excelforum.com/member.php...o&userid=29783
View this thread:
http://www.excelforum.com/showthread...hreadid=494903

Occurance Number Within a String
 

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.


--
JohnnyBGood
------------------------------------------------------------------------
JohnnyBGood's Profile: http://www.excelforum.com/member.php...o&userid=29783
View this thread: http://www.excelforum.com/showthread...hreadid=494903

Occurance Number Within a String
 

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"
wrote
in message
...

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.


--
JohnnyBGood
------------------------------------------------------------------------
JohnnyBGood's Profile:
http://www.excelforum.com/member.php...o&userid=29783
View this thread:
http://www.excelforum.com/showthread...hreadid=494903

Occurance Number Within a String
 

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.


--
JohnnyBGood
------------------------------------------------------------------------
JohnnyBGood's Profile: http://www.excelforum.com/member.php...o&userid=29783
View this thread: http://www.excelforum.com/showthread...hreadid=494903