How to find part of a string in another sheet

Options

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!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    edited 05/13/22
    Options

    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!

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    edited 05/13/22
    Options

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    Options

    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!

  • Genevieve P.
    Options

    I'm glad that worked for you! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!