Discussions

 View Only
Expand all | Collapse all

Creating a formula field to create text with leading 0's

  • 1.  Creating a formula field to create text with leading 0's

    Posted 04-27-2018 23:32
    Looked and looked for my situation.  I have a customer that sends me reports from their system what the release number of their PO is to be 4 digits - this would include leading 0's when less than 1000.  Simple, just use a text field to upload - but - they mix the format some are 0010 and others are 10.  They also have blanks in their Release field.

    I am trying to create a formula field to convert the one filed into a corrected field.  It really does not matter if the original is a text or number field as long as I get the 0000 format in the second field. I set up the second field as a Text Formula field.

     Here is the formula I tried if the [PO RELis in text format: 
     If(Trim([PO REL]=""),"",Right("0000" & ([PO REL]),4)

    Here is the formula I tried if the [PO REL] is in number format:
    If(Not IsNull([PO REL]),"",Right("0000" & ([PO REL]),4)

    The Right("0000" & ([PO REL]),4) works great but the complete formula does not.

    Sure could use some help.

    Thank you,

    Scott Ervin


  • 2.  RE: Creating a formula field to create text with leading 0's

    Posted 04-28-2018 04:08
    Try this for the Text format version

    If(Trim([PO REL])<>"",Right("0000" & Trim([PO REL]),4))


  • 3.  RE: Creating a formula field to create text with leading 0's

    Posted 04-29-2018 13:49
    Thanks so much - Worked great!