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

Options

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

Tags:

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Mike Rini
    Options

    @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
    Ryan Sides ✭✭✭✭✭✭
    Options

    Happy to help! -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!