a column to have this format DD-DD-DDDD

[XXX_Rosy_XXX]

Hi I have a column which 73-02-0004.

how do i create a formulae if the column not in this format it will be blank.

I know how to do blank but not in this DD-DD-DDDD. Must be number in this format DD-DD-DDDD.

D stands for digits only. Please help

[Yoshiro_Fujimori]

Hi @XXX_Rosy_XXX ,

Though I do not use Designer Cloud, I guess the Formula tool is very similar to Designer Desktop.

I think this regular expression in Formula tool may work.

REGEX_Match([Field1], "\d{2}-\d{2}-\d{4}")

[XXX_Rosy_XXX]

Hi Thanks

How do i left it as it is when the format is DD-DD-DDDD correct as for the 

the rest which does not match leave at blank.

if REGEX_Match([Specific Location], "\d{2}-\d{2}-\d{4}"
else ""
ENDIF

[alexnajm · Accepted solution]

@XXX_Rosy_XXX you are missing a few parts to your IF statement, like the ending parentheses to your Regex_match and a true vs false condition. Try this instead:

IIF(REGEX_Match([Specific Location], "\d{2}-\d{2}-\d{4}"), [Specific Location], "")

[danilang · Accepted solution]

Hi @XXX_Rosy_XXX 

@alexnajm gave the inline format of the IF expression.  The more common and more readable(IMHO) format is 

if REGEX_Match([Specific Location], "\d{2}-\d{2}-\d{4}" then

   [Specific Location]
else

   ""
ENDIF  

Dan

[alexnajm · Accepted solution]

@danilang 's works too, and agreed that it's preferred! I just like using IIF if I am dealing with boolean statements 😊

Just make sure all parentheses are included!

[XXX_Rosy_XXX]

Thanks 

However I have another question.

How can I drop the last digits 

if it is in this format DD-DD-DDDDD

i would like to drop the last digit. So it will be DD-DD-DDDD

[dfurlow · Accepted solution]

if the data is always going to be reliably in that format, so x = "DD-DD-DDDDD" then i think you can just take left(x,10) to get the 10 leftmost digits.

Although you are using digits, it appears they are formattted as strings.

[danilang · Accepted solution]

Hi @XXX_Rosy_XXX 

The regex expression originally supplied by @Yoshiro_Fujimori matches the pattern DD-DD-DDDD exactly.  Anything else is rejected.  To match extra digits at end end change the expression to "\d{2}-\d{2}-\d{4,}"  This modification matches 4 or more characters at the end of the pattern.  Follow this with Left([Specific Location],10) in a formula tool to drop any extra digits.

Dan