Use substr like function to parse out text from a string

Options

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

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    roya ✭✭✭
    Answer ✓
    Options

    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

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    roya ✭✭✭
    Answer ✓
    Options

    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
    ker9 ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!