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?
You would add the Text formula to your Update property of the datacard.
@RandyHayes I have the formula working, when it is saved to my SharePoint list, it is still doesn't add the leading 0.
@RandyHayes I changed my SharePoint field to Text vs Number. Do I add this formula to the DataCardValue Default formua?
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"
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.
@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?
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
Thanks @timl. That's precisely what I had in mind when I asked about how the single character value should be displayed.
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??
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!)
WarrenBelz
146,743
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,079
Most Valuable Professional