How to extract part of a cell and remove the rest

Christiana Gkini

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!

Nick Korna

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! 😊

Nick Korna

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

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

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.