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 variablelength 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!