Regular Expressions (RegEx) with FIND function

Hi Team,

I'm guessing that SmartSheet doesn't support regex in formulas as I get an #UNPARSEABLE error when I try it but I thought I'd better post the question here before I make that assumption.

I have the following text in my "Subject" column:

[1751] - Guide Posts

With the formula:

=MID(Subject1, FIND("[", Subject1) + 1, 4)

I get the following returned:

1751

However when I change the formula to

=MID(Subject1, FIND("["[12], Subject1) + 1, 4)

I get #UNPARSEABLE

Am I right in assuming this means Smartsheet doesn't support regex?

Oh, I'd better add too that I did search the community but found very little mentioning regex here and nothing to help me.

Find more posts tagged with

Construction

Sheets

Formulas

IT & Operations

Comments

Mike Andreas

May I ask why you are adding the [12] AFTER the second quotation mark? Because I'm no programmer, I may just not be understanding that this is common syntax for a command in a programming language with which I'm not familiar. However, it would seem to me that you're getting #unparseable because you have characters outside of the second quotation mark...

May I ask what you are ultimately trying to accomplish? It looks like you're trying to extract the 4 characters after the first "[" in a text string?

Karl_In_Oz

Hi @Mike, thanks for your repsonse.

I am trying to extract the four characters after a "[" but only if they start with "1" or "2" I can't include the regex inside the quotation marks because they will be interpreted literally rather than as a regex statement.

J. Craig Williams

Karl,

No. Smartsheet does not support RegEx. As much as it might help some of us, don't expect it ever to. The stated design is "simple"

That said, there is a change coming (at least, they are were asking opinions on it) to the LOOKUP function to provide a configurable return when no match is found (more on that elsewhere)

This would, I think, provide a way to 'trick' the system into giving a means of a having some RegEx functionality. At least, in my Gedankenexperimente it does.

It certainly won't allow us to search for "["[12

Craig

Robert S.

Hello,

If you're looking to get the four characters after the "[" but only if they start with a 1 or 2, you can do this with a different formula. The formula I've written below will give you the 4 characters after the "[" only if the first character is a 1 or 2, and will leave the cell blank otherwise.

=IF(OR(LEFT(MID(Subject1, FIND("[", Subject1) + 1, 4), 1) = 1, LEFT(MID(Subject1, FIND("[", Subject1) + 1, 4), 1) = 2), MID(Subject1, FIND("[", Subject1) + 1, 4), "")

Karl_In_Oz

@Robert and @J. Craig Williams thanks very much for your respective responses. Apologies for my tardiness in responding - I had all notifications turned off.