Right formula
I am looking to extract the string of data to the right of the LAST "/".
Here is my formula
=RIGHT([HELPPhase]@row, FIND(" / ", [HELPPhase]@row))
HELPPhase cell contains the below;
Project Platform (20221107) / 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?
Best Answer

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 seldomused 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([HELPPhase]@row, LEN([HELPPhase]@row)  FIND("~", SUBSTITUTE([HELPPhase]@row, "/", "~", 2)))
The formula below will work dynamically instead of using the hardcoded 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([HELPPhase]@row, LEN([HELPPhase]@row)  FIND("~", SUBSTITUTE([HELPPhase]@row, "/", "~", LEN([HELPPhase]@row)  LEN(SUBSTITUTE([HELPPhase]@row, "/", "")))))
Will either of these formulas work for you?
Kelly
Answers

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 seldomused 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([HELPPhase]@row, LEN([HELPPhase]@row)  FIND("~", SUBSTITUTE([HELPPhase]@row, "/", "~", 2)))
The formula below will work dynamically instead of using the hardcoded 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([HELPPhase]@row, LEN([HELPPhase]@row)  FIND("~", SUBSTITUTE([HELPPhase]@row, "/", "~", LEN([HELPPhase]@row)  LEN(SUBSTITUTE([HELPPhase]@row, "/", "")))))
Will either of these formulas work for you?
Kelly

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!!
Help Article Resources
Categories
Check out the Formula Handbook template!