cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sanderson82
Level: Powered On

Format text in excel to 3 digits

Hi, I'm currently patching data to an excel table.  Once of the columns, Sequence is represented in the app as a 3 digit number i.e. 1 is 001, 2 is 002 etc etc

 

I have the value formatted as text (even in the patch)

Sequence: Text(Value(Sequence),"[$-en-GB]000")

However when it is patched to the excel table the values are back to 1,2,3 etc.

 

I've looked at the following thread but none of the suggestions work for me
https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Add-leading-zeros-to-digits-using-Te...

 

Any help much appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Level 8

Re: Format text in excel to 3 digits

@sanderson82 

Have you tried something like this yet?  In this example I intentionally did not convert sequence to a number.

 

If(Len(Sequence)=1,"00"&Sequence,
	If(Len(Sequence)=2,"0"&Sequence,
		If(Len(Sequence)=3,Sequence,false
		)
	)
)

 

You might need to change the sequence numbers back to Text in your Excel, and also changing the table column in Excel to Text before attempting my idea.  Let me know if it works!

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

 

View solution in original post

2 REPLIES 2
mdevaney
Level 8

Re: Format text in excel to 3 digits

@sanderson82 

Have you tried something like this yet?  In this example I intentionally did not convert sequence to a number.

 

If(Len(Sequence)=1,"00"&Sequence,
	If(Len(Sequence)=2,"0"&Sequence,
		If(Len(Sequence)=3,Sequence,false
		)
	)
)

 

You might need to change the sequence numbers back to Text in your Excel, and also changing the table column in Excel to Text before attempting my idea.  Let me know if it works!

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

 

View solution in original post

sanderson82
Level: Powered On

Re: Format text in excel to 3 digits

@mdevaney  Thankyou! That worked!  I've used your code but also, I have a flow that creates an excel from a template and then adds a table.  I went back to the orginal template and set the column to text instead of general, I'm thinking this was possibly the root cause of the issue

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 146 members 5,129 guests
Please welcome our newest community members: