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