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

  • 0
  • 1
  • Question
  • Updated 12 months ago
  • Answered
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
Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb

Posted 12 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Try this for the Text format version

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

Scott

  • 258 Points 250 badge 2x thumb
Thanks so much - Worked great!