PadLeft not working in expression

[justshowmethevideo]

Hi all,

I am currently attempting to clean some phone numbers in Alteryx using the Formula tool. One aspect of the cleaning involves finding numbers that start with "614" then trimming the "61" and then adding a "0" at the start of the number, so instead of numbers beginning with "614" they should start with "04". My expression in the Formula tool is as follows:

IF Length([mobile_nm])=11 AND
StartsWith([mobile_nm],"614") THEN
TrimLeft([mobile_nm],"61") AND

PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF

The issue is that the PadLeft function in this expression is not working correctly. What I end up with is a bunch of numbers that are "0" but what I am expecting to see is something like "04XXXXXXXX" (original form would be "614XXXXXXXX"). 

I have figured out how to fix this by creating a second expression after the first one that is as follows:

IF Length([mobile_nm])=9 AND
StartsWith([mobile_nm],"4") THEN
PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF

But I would prefer to keep it all in one expression. Is there an issue with my syntax in the first expression?

Thanks for any advice.

Expression that doesn't work properly:

Expressions that work properly but I would like to use one expression instead of two:

[DavidP]

Hi @justshowmethevideo 

The problem is with trying to use the "AND" expression for the true part of your if statement. You can only use AND within the condition part.

IF Length([mobile_nm])=11 AND
StartsWith([mobile_nm],"614") THEN
TrimLeft([mobile_nm],"61") AND

PadLeft([mobile_nm],10,"0") ELSE
[mobile_nm] ENDIF

You have to do it like this:

IF Length([mobile_nm])=11 AND StartsWith([mobile_nm],"614") THEN

Padleft(TrimLeft([mobile_nm],"61"),10,"0")

ELSE

[mobile_nm] ENDIF