CONTAINS query - Returning a word from text string & validating it is in a specific format

.Leon.

Hi All,

I have a column [TEXT]

In it may be something like the below in random order -

word1 word2 PID#XXXXXX; word3 word4

PID#XXXXXX; word1 word2 word3 word4

word1 word2 word3 word4 PID#XXXXXX;

I would like to extract the entire word that begins with PID# into a new column [PID]

So that [PID] would just display PID#XXXXXX;

The PID#123456; word type will always be written with no gaps but may be of varying length.

Then I would like to validate that the format of this word with ends with ";"

[Validation] TRUE/FALSE or YES/NO <-- I can easily extract the final character and do an if statement here.

I have been playing around with formulas to return the PID# value but can't seem to return the entire word?

Any help is appreciated - thanks!

Find more posts tagged with

Accepted answers

Leibel S

@.Leon.

I think the below would work for you. This would only pull up the 'PID' if it is properly formatted as you described, and will return "No Valid PID" if there is none.

=IFERROR(MID(TEXT@row, FIND("PID#", TEXT@row), FIND(";", TEXT@row, FIND("PID#", TEXT@row)) - FIND("PID#", TEXT@row) + 1), "No Valid PID")

Leibel S

@.Leon.

So lets add an IF statement :)

=IFERROR(IF(FIND(";", TEXT@row, FIND("PID#", TEXT@row)) = LEN(TEXT@row), MID(TEXT@row, FIND("PID#", TEXT@row), FIND(";", TEXT@row, FIND("PID#", TEXT@row)) - FIND("PID#", TEXT@row) + 1), MID(TEXT@row, FIND("PID#", TEXT@row), FIND("; ", TEXT@row, FIND("PID#", TEXT@row)) - FIND("PID#", TEXT@row) + 1)), "No Valid PID")

All comments

Kelly Moore

Hey @.Leon.

Try this to parse your string

=IFERROR(MID([TEXT]@row, FIND(CHAR(35), [TEXT]@row) - 3, FIND(CHAR(59), [TEXT]@row) + 1 - FIND(CHAR(35), [TEXT]@row) + 3), "No Match")

I'm using the the ASCII codes for the '#' and the ';' signs. You can find a list of those characters here. Note the page has a link for a more complete list if needed. This formula finds the requested characters and, to determine the number of characters needed for the MID function, does a subtraction of characters between the two special characters. The IFERROR function that's wrapped around the MID function will help prevent errors from being thrown if your [Text] isn't formatted right. If you want it to be blank in that case, remove the words No Match but leave the double quotes.

For your validation helper column, here's a formula. You could have it as a checkbox - replace the true with a 1. Whenever you use numbers, be sure not to enclose the with quotes.

=IF(RIGHT([Parsed Textstring]@row, 1) = ";", "True")

Do these formulas work for you?

Kelly

.Leon.

Hi Kelly - it works almost perfectly, the only slight hiccup I have is that in some lines the symbol # is in use earlier in the same cell entry;

EG;

#1345 PID#12345;

So whilst the PID# entry is appropriately entered, it still shows as a defect/error because (I am assuming here) it is comparing the first # value, rather than the searching explicitly for PID# and comparing to that - is there a way to solve for this?

Thank you so such for your assistance - I was actually reading through your response on a similar subject earlier while trying to solve myself.

.Leon.

I tried to update the formula to check for PID# using a concatenation but it didn't quite work either -

Changing FIND(CHAR(59) to FIND("PID" + CHAR(59)

Nor did

FIND(("PID" + CHAR(35))

seem to work.

Leibel S

@.Leon.

I think the below would work for you. This would only pull up the 'PID' if it is properly formatted as you described, and will return "No Valid PID" if there is none.

=IFERROR(MID(TEXT@row, FIND("PID#", TEXT@row), FIND(";", TEXT@row, FIND("PID#", TEXT@row)) - FIND("PID#", TEXT@row) + 1), "No Valid PID")

.Leon.

Thanks Leibel & Kelly!

Leibel - yours worked perfectly apart from one minor issue I noticed.

If the cell has an entry like the below - with a ";" - but then text immediately after it without a space-

word1 word2 PID#123456;ABCDEF word3 word4

Then the PID# value that is stripped displays --> PID#123456;

Which doesn't flag as an error, yet it doesn't actually end in ";" so it will cause an error downstream.

It should display the full value --> PID#123456;ABCDEF

As whoever entered the value made a typo and there should be a space after the ";"

Any thoughts on how to solve?

Thanks!

Leibel S

Just add the space into the formula:

=IFERROR(MID(TEXT@row, FIND("PID#", TEXT@row), FIND("; ", TEXT@row, FIND("PID#", TEXT@row)) - FIND("PID#", TEXT@row) + 1), "No Valid PID")

.Leon.

Love the thought - wish I had of thought of that myself.

That ended up causing more errors - because if it is the only value in the cell, or the last entry in the cell there is no 'space' per-se after it - causing it to flag as a defect even if it is correctly entered...maybe is there a way to do a comparison across the entire cell length? and if it is the last value in the cell then do X, if it isn't the last value in the cell do Y?

Or potentially some type of wildcard defect alert if there is any additional text after the ";"?

Leibel S

@.Leon.

So lets add an IF statement :)

.Leon.

Thanks Leibel & Kelly for your help - both ended up solving my needs in different ways!

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.