5

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.

Functionality
Industry
Department

Comments

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?

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.

In reply to by Karl_In_Oz

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,

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 and @J. Craig Williams thanks very much for your respective responses. Apologies for my tardiness in responding - I had all notifications turned off.