What is wrong with this RegEx if formula?

[kjvan026]

I have a series of data where sometimes there is a number that is at the beginning and sometimes at the end. When it is not included at all, I need to pull different information. However, when I complete this formula I get the error "Parse: Error at Char (109): Formula tried to apply string operator to Numberic value (EndsWith)"

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

Here is a small selections of my data:

9865265 FUTA Liability
August Nevada Mod Bus Tax

December Supplemental

Leave 1234756

I know it is with my Regex formula and figure I am overcomplicating the issue. When there is this 7 digit number, I just need to pull it out no matter where it is.

Other things to note, my formula is set to be V_WString and the column referenced is a V_String.

[CoG]

Remove the StartsWith() and EndsWith(), the function REGEX_Match() returns a boolean/numeric output (0 = false, -1 = true),

In doing so you will need to update the parameters for the Match function

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

Notice that we are coding the StartsWith() logic into the RegEx. The expression \d{7}.* , will check if the field [Description] begins with 7 digits.

This appears to be what you are trying to do. Based off of your formula (imagine that the first [description] was : "1234567 desc", your current code would be trying to evaluate:

If StartsWith([Description], -1) <- which doesn't make any sense.

Hope this helps and Happy Solving!

[kjvan026]

Okay, I see where I went wrong with that. Is there a better code to be able to pull out the number? 

[CoG]

I just read the bit about pulling out a 7 digit number if it exists anywhere, you can optimize your formula by using two different Regex functions:

IF REGEX_Match([Description], ".*\d{7}.*")

THEN REGEX_Replace([Description], ".*(\d{7}).*","$1")

ELSEIF Contains([Description], "Supplemental")
then "Supplemental"
elseif contains([Description],"Nevada")
then "Nevada Modified Business Tax"
else "No Description"
endif

The conditional above does the complete check and pulls out only the seven digit # if one is found. Note: I am using your logic, if other numbers can be present in your data (i.e. 8+ digit) then this Regex will break.

[kjvan026]

This is exactly what I need! Thank you!!