Right formula

PeggyLang

I am looking to extract the string of data to the right of the LAST "/".

Here is my formula

=RIGHT([HELP-Phase]@row, FIND(" / ", [HELP-Phase]@row))

HELP-Phase cell contains the below;

Project Platform (2022-1107) / Core Concept Design and Business Modeling / Creative Development and Attraction Design (Phase 2)

What I am getting back is;

d Attraction Design (Phase 2)

What I really want is;

Creative Development and Attraction Design (Phase 2)

How do I need to change my formula?

Kelly Moore

Hey @PeggyLang

The formula below takes advantage of the optional functionality within the SUBSTITUTE function to designate what occurrence of text you want to replace, if multiple occurrences exist. The tilde "~" is just a placeholder so there is something to substitute with. If your text might contain the "~", then find some other seldom-used character that will work instead.

This formula hard codes in the second occurrence of a slash. "/". This formula will work fine if there are ALWAYS 2 slashes - no more and no less. If this will always be true, this is the simpler formula to use.

=RIGHT([HELP-Phase]@row, LEN([HELP-Phase]@row) - FIND("~", SUBSTITUTE([HELP-Phase]@row, "/", "~", 2)))

The formula below will work dynamically instead of using the hard-coded 2. You will need to use this formula if the count of slashes in the text varies. The formula below will find the total number of slashes in the cell and then always find the last one. The logic is the LEN(your column)-LEN(Substitute([your column]...) piece of the FIND function calculates how many "/" are in the string. Think of this acting like a COUNTIFs function- and it tells the substitute function which of the "/" it should act on. The last "/" will always be equal to the total count of "/" found in your text.

=RIGHT([HELP-Phase]@row, LEN([HELP-Phase]@row) - FIND("~", SUBSTITUTE([HELP-Phase]@row, "/", "~", LEN([HELP-Phase]@row) - LEN(SUBSTITUTE([HELP-Phase]@row, "/", "")))))

Will either of these formulas work for you?

Kelly

Kelly Moore

Hey @PeggyLang

The formula below takes advantage of the optional functionality within the SUBSTITUTE function to designate what occurrence of text you want to replace, if multiple occurrences exist. The tilde "~" is just a placeholder so there is something to substitute with. If your text might contain the "~", then find some other seldom-used character that will work instead.

This formula hard codes in the second occurrence of a slash. "/". This formula will work fine if there are ALWAYS 2 slashes - no more and no less. If this will always be true, this is the simpler formula to use.

=RIGHT([HELP-Phase]@row, LEN([HELP-Phase]@row) - FIND("~", SUBSTITUTE([HELP-Phase]@row, "/", "~", 2)))

The formula below will work dynamically instead of using the hard-coded 2. You will need to use this formula if the count of slashes in the text varies. The formula below will find the total number of slashes in the cell and then always find the last one. The logic is the LEN(your column)-LEN(Substitute([your column]...) piece of the FIND function calculates how many "/" are in the string. Think of this acting like a COUNTIFs function- and it tells the substitute function which of the "/" it should act on. The last "/" will always be equal to the total count of "/" found in your text.

=RIGHT([HELP-Phase]@row, LEN([HELP-Phase]@row) - FIND("~", SUBSTITUTE([HELP-Phase]@row, "/", "~", LEN([HELP-Phase]@row) - LEN(SUBSTITUTE([HELP-Phase]@row, "/", "")))))

Will either of these formulas work for you?

Kelly

PeggyLang

@Kelly Moore

The second formula is what I needed as there are a variable # of " / " in the cells. THANK YOU! I don't believe I would have EVER come up with that.

Love this community!!