Numeric Like Text Format Conversion

Question

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 CENTERDESIRED FORMAT100112812813013055555522022600604004CALL CENTERCALL CENTERHQHQ

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

Thanks.

danilang · Accepted Answer

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

binu_acs · Accepted Answer

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

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