How can I find a position of a character in a string?

Mike Rini

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

Ryan Sides

@Mike Rini

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

Mike Rini

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

Ryan Sides

Happy to help! -Ryan

Cleversheet

@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.