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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!