community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More
SOLVED

Multiple IF and Startswith assessment

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!

Highlighted
Alteryx Certified Partner
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.
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')

 

Regex to Pad - Capture.PNGWorkflow

 

Hello @marqueecrew,

It works excellent.

 

Thank you so much, really appreciated!

 

Cheers,

Glo

Labels