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
-
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
-
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! 😊
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!