Add leading zeros to digits using Text

Like (0) Share

Report

Posted on 17 Apr 2019 20:14:14 by ChadVKealey

1,393

I've imported data from an Excel file into a PowerApp. One of the columns is called "SectionNumber" and it may be a 1, 2 or 3 digit number or a single text character. I want to format this such that if it's a number, it's always displayed as 3 digits, with leading zeroes as needed (e.g.: "1" would display at "001").

The formula I'm using for this is:

Text(SectionNumber,"[$-en-US]000")

I've also tried varying combinations of # and 0 inside the double quotes, but none are producing the output I want. What am I doing wrong here?

All responses (17)

Answers (1)

RandyHayes 76,287 Super User 2024 Season 1 on 27 Jan 2021 at 17:45:45

Like (0)

Report

Re: Add leading zeros to digits using Text

@kmw1130

You would add the Text formula to your Update property of the datacard.
kmw1130 930 on 27 Jan 2021 at 17:42:37

Like (0)

Report

Re: Add leading zeros to digits using Text

@RandyHayes I have the formula working, when it is saved to my SharePoint list, it is still doesn't add the leading 0.
kmw1130 930 on 27 Jan 2021 at 17:24:20

Like (0)

Report

Re: Add leading zeros to digits using Text

@RandyHayes I changed my SharePoint field to Text vs Number. Do I add this formula to the DataCardValue Default formua?
RandyHayes 76,287 Super User 2024 Season 1 on 27 Jan 2021 at 16:50:37

Like (1)

Report

Re: Add leading zeros to digits using Text

@kmw1130

A number column will never have leading zeros. Leading zeros is about "format" of a number. So, you will not be able to store a number as 01 in your data numeric column. You can store it in a text column as such.

If you are trying to go the other way with it - you have 1 in the number column and want to display 01 - then use the following : Text(yourNumberColumn, "00")

i.e. Text(1, "00") will show "01"
kmw1130 930 on 27 Jan 2021 at 16:45:14

Like (0)

Report

Re: Add leading zeros to digits using Text

I'm trying to do something similar but on a number field, for instance for Month (1 would be 01), etc. I have my number field set to only be 2 digits.
Mr-Dang-MSFT on 14 Dec 2019 at 16:39:08

Like (0)

Report
Re: Add leading zeros to digits using Text

@accorti wrote:

Will this method always add 2 - leading 0's to the input or will it adjust to fit the 3 digit criteria?

i.e.

1 = 001

2 = 002

...

10 = 010 or 0010

This returns the number 1 so that it is 3 digits long and fills in leading zeroes as needed: 001

Text(1,"000")

If you try the "000" format against a number that is longer than 3 digits, it will exceed 3 digits: 1000

Text(1000,"000")

What are you looking to do?
accorti 35 on 13 Dec 2019 at 13:08:51

Like (0)

Report

Re: Add leading zeros to digits using Text

Will this method always add 2 - leading 0's to the input or will it adjust to fit the 3 digit criteria?

i.e.
1 = 001
2 = 002
...
10 = 010 or 0010
LRVinNC 2,297 on 18 Apr 2019 at 13:29:48

Like (0)

Report

Re: Add leading zeros to digits using Text

Thanks @timl. That's precisely what I had in mind when I asked about how the single character value should be displayed.
RandyHayes 76,287 Super User 2024 Season 1 on 18 Apr 2019 at 13:22:49

Like (0)

Report

Re: Add leading zeros to digits using Text

Looks like I came late to this party and all the good snacks are gone!

I think @LRVinNC had my favorite brute force method. Then of course the problem came up about non-numerics being different.

@Mr-Dang-MSFT I have always loved loved loved using the Concat and Split functions on strings. I use that exact method on phone number validations and more. But, perhaps that's more dip than the chip can hold in this case (I must have a party on my mind now)

@v-xida-msft bringing it home now with a pretty basic If (with a bit of a lengthy if sandwiched in it - I'd hate to see that inner If statement if we were dealing with 10 zeros! )

And behold @timl comes to the party next to last and does a nice cleanup. Well done.

And so... @ChadVKealey lots of different snacks here to go with, which one will be your accepted solution??
Verified answer

timl 34,955 Super User 2025 Season 1 on 18 Apr 2019 at 10:23:12

Like (14)

Report
Re: Add leading zeros to digits using Text

Hi Guys,

I'm enjoying this conversation!

@ChadVKealey - I think you did the right thing by adding a column to Excel. It's the quickest and easiest fix.

But going back to the original question, it seems to me that the simple rule that we're looking for is this.

If (input is numeric) => convert it to a number and format it Else => leave it as it is

If that's the case, we can apply this type of formula (assuming our input comes from the TextInput1 control):

If(IsNumeric(TextInput1.Text),
Text(Value(TextInput1.Text),"000"),
TextInput1.Text
)

(Well done to @LRVinNC for the numeric formatting expression!)