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