Numeric Like Text Format Conversion

[Ewbkm]

Hi,

I have the cost center data, which come in as text data type and I need to convert the cost center in numeric form to be three digits format (1 to 001 and 22 to 022) and keep the cost center with text name as the way it is (e.g. HQ still shows as HQ).  The data and desired results are as below: 

COST CENTER	DESIRED FORMAT
1	001
128	128
130	130
555	555
22	022
60	060
4	004
CALL CENTER	CALL CENTER
HQ	HQ

Is there any formula can help me to do the conversion?

Thanks.

[Qiu]

@Ewbkm 
First we can check if the length of the text is below 3 or not. 
If yes, a PadLeft function can be used.

[danilang · Accepted solution]

Hi @Qiu 

Small error in your formula causing HQ to become 0HQ.  Change your condition to use regex_match looking for a digit as the first character

if regex_match([COST CENTER],"\d.*") then 
   PadLeft([COST CENTER], 3, '0') 
else 
   [COST CENTER] 
endif

 Dan

[Qiu]

@danilang 
Thank you so much for pointing it out.
So sorry missing that out.

[binuacs · Accepted solution]

@Ewbkm another way of doing is with the IsInteger() function

IIF(IsInteger([COST CENTER]),PadLeft([COST CENTER], 3, '0'),[COST CENTER])