How to find part of a string in another sheet

Frank Falco

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?

Genevieve P.

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

Frank Falco

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.

Frank Falco

@Genevieve P. , @Paul Newcome @Andrée Starå do you have any suggestions on this one?

Genevieve P.

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

Frank Falco

Thank @Genevieve P. that worked a treat.

I really appreciate your help.

Genevieve P.

I'm glad that worked for you! 🙂