Extracting part of text with a formula

Options
This discussion was created from comments split from: Returning text using IF Function.

Answers

  • Katrina Walker
    Options

    @Christa Brown we are trying something similar to return a string after finding a set word but could not get it to work so would you please share how you got it to work with MID rather than RIGHT please.

    =IF(CONTAINS("Data Collection", [Action Comments History]@row), RIGHT([Action Comments History]@row, LEN([Action Comments History]@row) - FIND("[", [Action Comments History]@row + 1), ""))

    Trying to get the string of words after the words Data Collection up until the next [.

    Thank you!

  • Genevieve P.
    Options

    Hi @Katrina Walker

    Can you post the format of the Text String you're looking in to?

    For example, if your text string looks like this:

    Data Collection [this that] and more

    And you're looking to find the text that's only in-between [these], then try this:

    =IF(CONTAINS("Data Collection", [Action Comments History]@row), MID([Action Comments History]@row, FIND("[", [Action Comments History]@row) + 1, FIND("]", [Action Comments History]@row) - FIND("[", [Action Comments History]@row) - 1))

    Let me know if this makes sense!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Katrina Walker
    Options

    Hi Genevieve, apologies for the delay in responding and thank you so much for your assistance. I am just trying this out now and will let you know how it goes as think it is what I need. If not then i will post an excerpt of the data for you.

    Thanks

    Katrina

  • Katrina Walker
    Options

    @Genevieve P. Hi again Genevieve. I nearly got this formula to work but I had to change the delimiter to find the correct string.

    To explain a little more, I am bringing in a whole screen of text from another system that is updated throughout the life of an incident and I want to pull the last data collection comments. The system that the report comes from always adds in [Date & Email Address] for each comment added. I was trying to use your formula to find data collection then next open [ and work back the way to the end of data collection but I think as there are too many [ ] it only find part of the string.

    When I changed it and added a * before data collection I got the correct string. So in the example below I get back 16 different note versions - Approx. 1-2 weeks for DC activity. which is exactly what I want


    Cell contents

    *Data Collection: 16 different note versions - Approx. 1-2 weeks for DC activity.

    [JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

    *Data Collection: 16 different note versions - Approx. 4-5 weeks for DC activity.

    [JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

    *Data Collection: 16 different note versions - Approx. 8-9 weeks for DC activity.

    [JUL-21-21 rrxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning*


    But if I try to use the following (which is the way that it does import to the cell) I cannot get it to work

    Cell contents

    [JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 1-2 weeks for DC activity.

    [JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

    [JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 4-5 weeks for DC activity.

    [JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

    [JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 8-9 weeks for DC activity.

    [JUL-21-21 rrxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning*

    Thanks again for your help!

    Katrina

  • Keith Gemmell
    Keith Gemmell ✭✭
    edited 02/21/22
    Options

    @Genevieve P. @Katrina

    Hello Katrina, did you manage to get this work as i am looking to do something exactly the same?

    Looking for both your assistance if you don't mind?


    Many thanks in advance

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!