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

Find more posts tagged with

Accepted answers

ker9

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!

roya

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)

All comments

ker9

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!

roya

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)

ker9

@roya - Perfect!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.