Skip to main content

I am trying to create a logic to extract part of a text. For example, here:

“41111 Revenue : Subscription : Platform”

I want to transform the above text as: “41111 - Platform”. The issue is, when I use FIND function to find “:” symbol, it only returns the position if the first occurrence. But, I want to return the position of the first occurrence from the end. 

I could use nested IFs to find the first occurrence, slice the text, and find first occurrence from the sliced text. However, some accounts have 3 and more “:” symbols:

51111 Cost of Revenue : Subscription : Allocation In - Subscription : Allocation In - Sales & Marketing

Is there a way to locate the last occurrence of the text, instead of the first?

Hi @Dastan S. ,

Imagine we have  a metric named “Text” for this case, let’s include your example as well:


Overall, my suggested formula would be:
RIGHT( Text, LEN(Text) - FIND("*", TRIM(SUBSTITUTE(Text, ":","*", LEN(Text) - LEN(SUBSTITUTE(Text,":",""))))))

Let’s break this down to understand this better:
1. LEN(Text) - LEN(SUBSTITUTE(Text, ":", ""))
Determines the number of ":" characters present in the given string, the result in this case is 3.

2. SUBSTITUTE(Text, ":","*", LEN(Text) - LEN(SUBSTITUTE(Text, ":", "")))
Replaces the last ":" character with “*”, the result in this case is:
51111 Cost of Revenue : Subscription : Allocation In - Subscription * Allocation In - Sales & Marketing

3. With above adjustment, we now can easily identify “*” in the string and then apply further TRIM, FIND, RIGHT.. functions to get the final result we want, aka " Allocation In - Sales & Marketing"

Hope my answer helps 😊

Awesome, thank you Thu, this worked! Didn’t know about SUBSTITUTE formula.

Reply


Ask your question here!

Terms & ConditionsCookie settings