Add leading zeros to digits using Text

[ChadVKealey]

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?

[LRVinNC]

Try:  Text(Value(yourControl.Text), "000")

Or, the brute force method, 
// add leading zeros then trim to keep right 3 digits
Right(Concatenate("0000", yourControl.Text),3)

[Mr-Dang-MSFT]

+1 to @LRVinNC 's suggestion--you're on a roll with the formulas! 🙂

Big idea here: By default, your data is text. But you can only use # and 0 to format text in the way you want when its first argument is a number.

So you need to turn the data into a number, then return it back to text in the format you desire. To maintain 0s, you'll use leading 0s as in @LRVinNC's example.

[ChadVKealey]

---

@Mr-Dang-MSFT wrote:

+1 to @LRVinNC 's suggestion--you're on a roll with the formulas! 🙂

Big idea here: By default, your data is text. But you can only use # and 0 to format text in the way you want when its first argument is a number.

So you need to turn the data into a number, then return it back to text in the format you desire. To maintain 0s, you'll use leading 0s as in @LRVinNC's example.

---

Well, that first suggestion didn't work. And the problem with turning the data into a number is that it's not always a number. As originally stated, the values in that field (in the collection) can be 1 to 3 digits OR a single-character string. It's the section designator for courses at our university, so lecture/recitations have a numeric section ID, but lab classes have a letter. 

I was hoping to work with the Excel data as delivered by our reporting system and do the manipulation in PowerApps, but I guess I'll need to do some of it in Excel.

-Chad

[LRVinNC]

So let's say the numeric section ID is 1 -- you want to see 001.  And let's say the lab section ID is the single character A.  What do you want to see then?  Still just A or 00A? 

[Mr-Dang-MSFT]

This is a very fun problem and I'm getting giddy 🙂

There's lots of requirements here. Let me share a big idea and I'd love it if @LRVinNC @RandyHayes @v-xida-msft can add more to the formula and their take.

Big idea: I can Split() a string of text so that each character is its own row in a table.

Split("123abc","")

This formula would return a table of 6 records. It would have a column called "Result" that would contain the individual character that had been split: 1, 2, 3, a, b, c. Here the delimiter is "" which indicates split every individual character.

Now that each character is in a table, you could do many things. You could filter the table where each row matches certain criteria. IsMatch can check if something is a digit or a letter, etc. You could count how many characters fit the criteria.

Then after you filter the rows you want, you could concatenate the filtered set again. Maybe you had wanted to filter out all the digits into one table. You can concatenate that subset again, convert them to a value, and format them to include leading zeroes.

Text(Value(Concat(Filter(Split("123abc",""),IsMatch(Result,Digit)),Result)),"0000")

There's a few parts to this one. Reading inside out--some Excel work 🙂

• Split: described earlier
• Filter: filters the characters to return only characters that are digits
• Concat: collapses the table into a string again
• Value: makes the digits into numbers (since they had been concatenated, they are strings)
• Text: same formula @LRVinNC shared earlier

Next you could append the filtered characters that were not digits and run through a similar process.

[ChadVKealey]

Just "A". After spinning wheels on this for two hours today, I added a column to the Excel sheet to give me what I wanted in about 2 minutes. So, for this term, the problem is solved, but I'd like to find a way to do this strictly in PowerApps for the next go-around in a few months.

[v-xida-msft]

Hi @ChadVKealey ,

I have made a test on my side, please take a try with the following workaround:

Set the Text property of the Label to following:

If(
    IsNumeric(TextInput1.Text),
    If(
        Len(TextInput1.Text)=1,
        "00"&TextInput1.Text,
        Len(TextInput1.Text)=2,
        "0"&TextInput1.Text,
        TextInput1.Text
    ),
    TextInput1.Text
)

On your side, you should type following (if the SectionNumber column is a Text type column in your Excel table😞

If(
    IsNumeric(SectionNumber),
    If(
        Len(SectionNumber)=1,
        "00" & SectionNumber,
        Len(SectionNumber)=2,
        "0" & SectionNumber,
        SectionNumber
    ),
    SectionNumber
)

If the SectionNumber column is a Number type column in your Excel table, please modify above formula as below:

If(
    IsNumeric(SectionNumber),
    If(
        Len(Text(SectionNumber))=1,
        "00" & SectionNumber,
        Len(Text(SectionNumber))=2,
        "0" & SectionNumber,
        SectionNumber
    ),
    SectionNumber
)

Best regards,

[timl · 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!)

[RandyHayes]

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??