How to find part of a string in another sheet
I have a sheet with a string, say xyz, this is not always the same length.
In another sheet an have a column of numbers, which have values which are variable-length strings with values which in the case of the xyz is abcd123xyz
I need to find the row that has the xyz is the column.
Is there a way to do this?
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Best Answer
-
Hi @Frank Falco
You can use the CONTAINS Function to see if a string contains the values you specify.
I would personally put this in a JOIN(COLLECT just in case more than one cell contains that string - this way I can look at all possible options and evaluate:
=JOIN(COLLECT({Column to Return}, {Sheet B Column V}, CONTAINS([Column V]@row, @cell)), ", ")
Cheers,
Genevieve
Answers
-
So to clarify:
Sheet A: [Column R] contains xyz (not always the same length) in a row
{Sheet B Column V}: Has [Column V] has values that are longer (abcd123xyz), xyz is not always at the end of the string
I need to find the row in Column V that contains xyz so I can get the index to get the value in another column
Index /Match will need to be in there somewhere I assume, but I cannot make that work.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
@Genevieve P. , @Paul Newcome @Andrée Starå do you have any suggestions on this one?
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Hi @Frank Falco
You can use the CONTAINS Function to see if a string contains the values you specify.
I would personally put this in a JOIN(COLLECT just in case more than one cell contains that string - this way I can look at all possible options and evaluate:
=JOIN(COLLECT({Column to Return}, {Sheet B Column V}, CONTAINS([Column V]@row, @cell)), ", ")
Cheers,
Genevieve
-
Thank @Genevieve P. that worked a treat.
I really appreciate your help.
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
I'm glad that worked for you! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!