# Alteryx Designer Discussions

The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

## Multiple IF and Startswith assessment

Highlighted
5 - Atom

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?

Highlighted
Alteryx Certified Partner
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
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner

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

Highlighted
5 - Atom

Hello @marqueecrew,

It works excellent.

Thank you so much, really appreciated!

Cheers,

Glo

Labels