Use substr like function to parse out text from a string
Hello
I am trying to parse this text out of a string- Column Name is Billing Cost Allocation Fields
See example below
Here I want to retrieve just "T-07460" from below
T-Code - Child OpUnit:T-07460-001 :ADC FACfn Reinsurance Exchange-Init
I tried to do =VALUE(RIGHT([Billing Cost Allocation Fields]@row LEN([Billing Cost Allocation Fields]@row) - FIND("T-Code - Child OpUnit:" [Billing Cost Allocation Fields]@row)))
Not sure how I can do this. Please do help me
Best Answers
-
Hi @roya
If your text string is always in the exact same format, this should work:
=MID([Billing Cost Allocation Fields]@row, FIND(":", [Billing Cost Allocation Fields]@row) + 1, FIND(" :", [Billing Cost Allocation Fields]@row) - FIND(":", [Billing Cost Allocation Fields]@row) - 5)
We are finding the first colon (:), the second colon with space in front ( :) and subtracting 5 characters from what we find to get to the text that you want to see.
Hope this helps!
-
THANK YOU!!!! so much. This worked!!!! Really grateful
Thanks for the detailed explanation of how it works.
Your solution is elegant and inspired me to try this , which worked as well as Text string is fixed
=MID([Billing Cost Allocation Fields]@row, 23, 7)
Answers
-
Hi @roya
If your text string is always in the exact same format, this should work:
=MID([Billing Cost Allocation Fields]@row, FIND(":", [Billing Cost Allocation Fields]@row) + 1, FIND(" :", [Billing Cost Allocation Fields]@row) - FIND(":", [Billing Cost Allocation Fields]@row) - 5)
We are finding the first colon (:), the second colon with space in front ( :) and subtracting 5 characters from what we find to get to the text that you want to see.
Hope this helps!
-
THANK YOU!!!! so much. This worked!!!! Really grateful
Thanks for the detailed explanation of how it works.
Your solution is elegant and inspired me to try this , which worked as well as Text string is fixed
=MID([Billing Cost Allocation Fields]@row, 23, 7)
-
@roya - Perfect!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!