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

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.