Concating string based on the condition of sting length

Question

Hello!

I recently got the below dataset and would like to do some transformation:

NameGroup NoJohn123Sam5647Apple000458Shally679412

For the column `group no`, I have to make sure the length is 6. If this is not the case, we add '0's before the string.

So I try to write the below formula but it does not work

IF Length([Group No]) < 6
THEN (6 - Length([Group No]) * "0" + [Group No]
ELSE null()
ENDIF

The expected result would be:

NameGroup NoJohn000123Sam005647Apple000458Shally679412

Any ideas on how to do that are highly appreciated.

Thank you,

ShankerV · Answer

Hi @edward_chow

One way of doing this is with the help of padleft function.

PadLeft([Group No], 6, '0')

Note: Padleft function works only on the String datatype.

If your column Group No is Numeric datatype. Please convert into String datatype using Multi Field Formula as below.

Many thanks

Shanker V

Concating string based on the condition of sting length.yxmd

niteshsharma677 · Answer

Hi @edward_chow

You can use the PadLeft Function in the Formula tool to get the desired result like below.

Formula - PadLeft([Group No], 6, '0')

flying008 · Answer

Hi, @edward_chow

This is your want ?

PadLeft([Group No], 6, "0")

******

If it can help you , please mark it as a solution and give a like for more share.