How can I find a position of a character in a string?
Hi all 
I'm trying to find a position of a character within a string, which in theory seems simple  just use the FIND() function.
What I'm struggling with is when there are multiple instances of the value, and I want to start at the 4th instance (for an example) of it. Here's an example of the String I'm trying to parse:
EASEFESE_AS_AV_TX_RT_RT_NI_MI
In this case, I want to get to the first bolded RT. FIND() is perfect for getting me the starting position of the first "_", but I'm trying to figure out how I can get to the 4th "_" without hardcoding positional starts as the first part the string will be variable in length.
Thanks for any/all help.
Mike
Answers

Hi Mike, this gets complex pretty quickly but here goes:
You'll need to use a combo of Left, Mid, and Right along with nested Finds.
For example, let's say you wanted to find the first one from the left:
=LEFT(MasterString@row, FIND("_", MasterString@row, 1)  1)
Then you wanted to find the next one:
=MID(MasterString@row, FIND("_", MasterString@row, FIND("_", MasterString@row)) + 1, FIND("_", MasterString@row, FIND("_", MasterString@row) + 1)  FIND("_", MasterString@row)  1)
Then you wanted to find the third one:
=MID(MasterString@row, FIND("_", MasterString@row, FIND("_", MasterString@row) + 1) + 1, FIND("_", MasterString@row, FIND("_", MasterString@row, FIND("_", MasterString@row) + 1) + 1)  FIND("_", MasterString@row, FIND("_", MasterString@row) + 1)  1)
And so on. Until you want to find the last one:
=RIGHT(MasterString@row, LEN(MasterString@row)  FIND("_", MasterString@row, (FIND("_", MasterString@row, FIND("_", MasterString@row) + 1) + 1)))
I hope that gives you a place to start. I created this for a client and I burned many brain cells trying to get it right!
Ryan

@Ryan Sides Thank you! I'm ok with complex if it finds a solve. I'm going to try rewriting my parsing this weekend now  much appreciated! I was starting to wonder if there was even a way, so I appreciate this help.

@Mike Rini, I assume you've probably solved this by now, but here are a couple things to try. Use the applicable formula in a helper column, and then use the resulting anchor value in whatever formula you then use to orient from that position.
Where [MasterString]@row = EASEFESE_AS_AV_TX_RT_RT_NI_MI
To locate the first instance of "RT" at position 19: =FIND(CHAR(140), SUBSTITUTE([MasterString]@row, "RT", CHAR(140), 1))
To locate the second instance of "RT" at position 22: =FIND(CHAR(140), SUBSTITUTE([MasterString]@row, "RT", CHAR(140), 2))
To locate the fourth instance of "RT" at position 18: =FIND(CHAR(140), SUBSTITUTE([MasterString]@row, "_", CHAR(140), 4))
NOTE: The search term is case sensitive. "rt" will return 0 where "RT" returns 19.
NOTE: In text formulas you can orient both positively and negatively from whatever anchor position you've specified.
Help Article Resources
Categories
Check out the Formula Handbook template!