Darshan Hiranandani : Issue with RegEx in Formula - Error When Trying to Extract 7-Digit

[darshanhiranandani24]

Hi all,

I’m Darshan Hiranandani, working with a formula that uses RegEx to extract a 7-digit number from the Description field, but I’m running into an error: "Parse: Error at Char (109): Formula tried to apply string operator to Numeric value (EndsWith)."

Here’s the formula I’m working with:

if StartsWith([Description], RegEx_Match([Description], ".*\d{7}.*"))
then Left([Description], 7)
Elseif EndsWith([Description], RegEx_Match([Description], ".*\d{7}.*"))
then Right([Description], 7)
elseif Contains([Description], "Supplemental")
then "Supplemental"
elseif contains([Description],"Nevada")
then "Nevada Modified Business Tax"
else "No Description"
endif

Problem:

• I’m trying to extract the 7-digit number from the description regardless of whether it appears at the start or the end.
• The error suggests an issue with the EndsWith function and applying a string operator to a numeric value, which I suspect is related to my RegEx match.

Some Sample Data:

• 9865265 FUTA Liability
• August Nevada Mod Bus Tax
• December Supplemental
• Leave 1234756

My goal:

• If there’s a 7-digit number anywhere in the description, I want to extract it (no matter if it’s at the start or the end).
• Otherwise, if the description contains the word "Supplemental," I return "Supplemental."
• If it contains "Nevada," I return "Nevada Modified Business Tax."
• If none of the conditions are met, I want it to return "No Description."

I’m wondering if I’m overcomplicating things with the regular expression or the logic in my formula. Does anyone have suggestions for simplifying this or fixing the error?

Any help would be much appreciated!

Regards

Darshan Hiranandani

Regards

Darshan Hiranandani

[Tokimatsu]

I used your code and rewrote it as following. regex_match returns true or false, not string.

if RegEx_match([Description], "^\d{7}.*")
then Left([Description], 7)
elseif RegEx_match([Description], ".*\d{7}$")
then Right([Description], 7)
elseif Contains([Description], "Supplemental")
then "Supplemental"
elseif contains([Description],"Nevada")
then "Nevada Modified Business Tax"
else "No Description"
endif