cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChadVKealey
Power Participant
Power Participant

Add leading zeros to digits using Text

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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!)

View solution in original post

17 REPLIES 17
LRVinNC
Super User
Super User

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)

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

+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.


@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

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? 

 

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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.

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
Community Support
Community Support

Hi @ChadVKealey ,

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!)

View solution in original post

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! Smiley LOL)

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,575)