If Project name column CONTAINS specific text

Okay I have 2 columns, one contains a unique number (left) and on the right that unique number is listed with the program. I have been able to accomplish this so far, however I ran into a little snag. There are some programs that contain JNEEO, and their unique TDL #. However, once I added the formula I discovered not all followed this pattern. My current formula is below, and 14 is the # of characters that contain the "prefix", which is "JNEEO TDL 101 " for example. Please note there is a space at the end to keep everything clean. Now what I need is the addition of an if statement to be something like:

If CELL X does not contain/begin with "JNEEO TDL" then use the cell value as is.

=REPLACE([Project Name (Official)]@row, 1, 14, "")

Find more posts tagged with

Formulas

Accepted answers

Gia Thinh

It's strange!
I have tested this formula at my side and it worked well.

=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)

If it's possible, could you share your example sheet with me (thinh.huynh@giathinh.tech) so that I could look into it for you?

All comments

Gia Thinh

@SherryFox,

Try this formula:

=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)

Hope it works for you.

SherryFox

@Gia Thinh ,

This seems almost correct, I have circled 2 items that do NOT have "the prefix" in their name, in these instances, the entire name from the column on the left should be displayed. The rest of your formulas is correct.

Gia Thinh

Did you convert the new formula to column formula to take effect on all rows?

SherryFox

@Gia Thinh ,

I just did the conversion to column formula, and there was no change.

Gia Thinh

Can you provide a screenshot with the new column formula?

SherryFox

Gia Thinh

It's strange!
I have tested this formula at my side and it worked well.

=IF(CONTAINS("JNEEO TDL", [Project Name (Official)]@row), REPLACE([Project Name (Official)]@row, 1, 14, ""), [Project Name (Official)]@row)

If it's possible, could you share your example sheet with me (thinh.huynh@giathinh.tech) so that I could look into it for you?

SherryFox

@Gia Thinh ,

PERFECT!!!!! Thanks so very much. Here is a screenshot showing the results.

Gia Thinh

Great to hear it 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.