Find Location of a Specific Character in a Cell With Multiple Occurrences of that Character

[jeffgibson55]

I'm using Excel 2007. I'm looking for a way to reference a specific cell to determine the exact location of a specific character that occurs multiple times in the string. In this case I want the numeric location of the last occurrence of the character "-" so I can extract all the text to the right of it. I've done this in the past using the find function but that always starts left to right and in my data set each cell has a different number of occurrences of the delimiting text ("-") and in this case I only care about the last occurrence of this character.

For what it's worth I'm able to count the number of the occurrences of the character ("-") in a cell with this formula: =SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,"text","")))/LEN("text") where "range" is the cell and "text" is "-", but I don't know how to find the position of the final occurrence of "-".

Any help would be most appreciated.

Thanks,
Jeff

 

[Jonmo1]

Welcome to the board...

If you already have a formula to COUNT the number of occurances, then you can do this..

=SEARCH("^^",SUBSTITUTE(range,"text","^^",countofoccurances))

Where ^^ is simply any string of your choice that is most UNlikely to actually appear in the original string.

Hope that helps.

 

[VoG]

Or maybe like this

Excel Workbook
	A	B	C
1	abc-def-ghi		ghi
Sheet4

 

[jeffgibson55]

Thanks jonmo1 and VoG - I really appreciate the help and the quick replies. Both of your solutions worked perfectly - I think I'll go with VoG's just because it's a shorter formula but I got the same results with both. Thank you both for the education and for saving me a lot of time I would have otherwise spent dinking around with this and probably ending up manually running a text to columns on my data set!!!

Much Thanks!
Jeff