Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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.

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.

  • J. Craig Williams
    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.
    Robert S. Employee

    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), "")

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

This discussion has been closed.