Multiple IF and Startswith assessment

[Gloaisiga]

Hello,

I have a process, where I have to validate that a column contains 9 characters. Normally, the value has 8 numbers and 1 letter. The letter can be at the beginning or at the end of the value. If the value does not have 9 characters I have to add a "0" at the beginning of the value, however, when the letter is at the beginning I need to add the 0 after the letter. for instances: A1234567 then I need the new value as A01234567.

I have tried with the formula IF StartsWith([Tax Number 1], "A") AND Length([Tax Number 1])=!9 THEN Replace([Tax Number 1], "A", "A0") ELSE [Tax Number 1] ENDIF and it works fine but, just for one option.

I mean, if I enter the formula as follow, I get a value of "0" instead of the value needed.

IF StartsWith([Tax Number 1], "A") OR StartsWith([Tax Number 1], "B") OR StartsWith([Tax Number 1], "C") OR StartsWith([Tax Number 1], "D") OR StartsWith([Tax Number 1], "E") and Length([Tax Number 1])!=9 THEN Replace([Tax Number 1], "A", "A0") OR Replace([Tax Number 1], "B", "B0") OR Replace([Tax Number 1], "C", "C0") OR Replace([Tax Number 1], "D", "D0") OR Replace([Tax Number 1], "E", "E0") ELSE [Tax Number 1] ENDIF

Could someone help me with this inconvenience? How can create a formula that recognizes the letters from A to Z and if it is less than 9 characters can add a 0 after the letter?

Thanks in advance!

[MarqueeCrew · Accepted solution]

If
Regex_match([tax number1],"\u\d+") then
Left([tax number 1],1) + padleft(Substring([tax number 1],1),8,"0")
Else
Padleft(regex_replace([tax number 1],"\D",""),8,"0") + right([tax number 1])
Endif

I'm watching a movie now. I think that this will work.

Cheers,

Mark

[PhilipMannering]

Hi Gloasiga,

This will only fill pad out one missing 0 (and won't pad 2 or more zeros unlike @MarqueeCrew's superior solution). But here's how you can do it in one expression,

REGEX_Replace([Field1], '\b(\D?)(\d{7})(\D?)\b', '\10\2\3')

Workflow

[Gloaisiga]

Hello @marqueecrew,

It works excellent.

Thank you so much, really appreciated!

Cheers,

Glo