How to extract part of a cell and remove the rest

Hello

I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and I want to extract to another column only the text before the word 'request '. Could you please help me how to do this? Thank you so much!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Christiana Gkini,

    If the format of the entries is consistent then you can use a formula similar to this:

    =LEFT([Column to check]@row, (FIND("request", [Column to check]@row) - 2))

    Example:

    Obviously you will probably need to change the "Column to check" in the formula, but this should be straightforward enough.

    Hope this helps; any questions or issues then just post! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Christiana Gkini,

    If the format of the entries is consistent then you can use a formula similar to this:

    =LEFT([Column to check]@row, (FIND("request", [Column to check]@row) - 2))

    Example:

    Obviously you will probably need to change the "Column to check" in the formula, but this should be straightforward enough.

    Hope this helps; any questions or issues then just post! 😊

  • Christiana Gkini
    Christiana Gkini ✭✭

    Thank you very much Nick! That worked!

    Also if I could ask for your help with a countif formula in smartsheet when you have over 100 different values, how could I use that to count how many times the values appear in my list?

    Looking forward for your response .

    Thank you!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    I'd probably use one of the solutions similar to here to get the list of unique values in a new sheet:

    Get a list of Distinct / Unique values β€” Smartsheet Community

    Then do a COUNTIF using the data with the criterion being the unique value @ row.

    This is down to the large number of values - if I've misunderstood and you don't have 100 unique ones, then using the COUNTIF in the summary and a sheet summary report would be an alternative.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!