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?

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.