cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rickvvaldez
Regular Visitor

Excel: How to convert range of numbers to percentages of rounded to 1 decimal (0.0%)?

Hello community,

 

How do I convert a range of numbers to percentages rounded to 1 decimal (0.0%) in Power Automate Desktop?

Your help is very appreciated.

 

Thank you

Rick

1 ACCEPTED SOLUTION

Accepted Solutions
VJR
Super User
Super User

Hi @rickvvaldez 

 

I have written a vbscript which can be utilised as a customised function and can work for ANY excel file path, ANY sheet, From ANY cell, To ANY cell, ANY number of decimal places (one or two as you needed these two options).

 

VJR_0-1646108255708.png

 

This is how the overall Flow looks like.

 

VJR_1-1646108376430.png

 

 

Before running the Flow

 

VJR_2-1646108426092.png

 

 

After running the Flow for 2 decimal places

 

VJR_3-1646108486603.png

 

 

Simply change your inputs as per your needs in the Set variable statements.

 

Below is the script for the vbscript action.

 

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")

objWorkbook.Sheets("%SheetName%").Activate

If "%SingleDecimal%" = True then
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.0%%"
Else
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.00%%"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing

 

Alternatively:

Paste the below code in a blank flow editor, change the Excel path, set the values of the desired variables and run the flow

 

SET ExcelFilePath TO $'''C:\\test\\filename.xlsx'''
SET SheetName TO $'''Sheet1'''
SET FromRange TO $'''A1'''
SET ToRange TO $'''A3'''
SET SingleDecimal TO $'''False'''
Scripting.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFilePath%\")

objWorkbook.Sheets(\"%SheetName%\").Activate

If \"%SingleDecimal%\" = True then
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.0%%\"
Else
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.00%%\"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError

 

View solution in original post

7 REPLIES 7
mdevaney
Super User
Super User

@rickvvaldez 

Could you please be more specific.  Where does this range of numbers exist?  If it helps to explain, share a screenshot. 

MichaelAnnis
Super User
Super User

I would attempt the following assuming you have already launched excel and the current excel instance is in focus:


Go to the Home tab on the ribbon (the top menu items) -> under number formatting you should see an icon for %. This defaults to 0 decimal places. You should see another icon for Left by 1 decimal place. 

set up your flow like this:

 

Select cells

click UI element in window (the % sign)

click UI element in window (the increase decimal place)

 

should be done. 

Note:  if you want a whole column, the best way would be to click UI element in window (column c) instead of select cells. 

good luck!

VJR
Super User
Super User

@rickvvaldez , yes please. could you share some more details where is the data coming from and what type of variable is it currently in.

 

Also please confirm if the below is what you are looking for?

Example 1:

Input: 10.23

Output: 10.2%

 

Example 2:

Input: 20

Output: 20.0%

 

Please share all possible scenarios you have for the input data.

 

rickvvaldez
Regular Visitor

Hello helpful community,

Below is the before and after that I am attempting. The numbers a of type General to be converted to Decimal.

The goal is to convert a range of cells into percentages with one ( and two ) decimal places.

 

Percent-before.pngPercent-after.png

The solution I gave earlier should work, here is some more added to it:

 

After launch Excel

Get First Free Column/Row to %FirstFreeColumn% and %FirstFreeRow%

Select cells from A2 to (whatever the last column is, if it moves, try %FirstFreeColumn - 1%) %FirstFreeRow%

click UI element in window (the % sign)

click UI element in window (the increase decimal place)

VJR
Super User
Super User

Hi @rickvvaldez 

 

I have written a vbscript which can be utilised as a customised function and can work for ANY excel file path, ANY sheet, From ANY cell, To ANY cell, ANY number of decimal places (one or two as you needed these two options).

 

VJR_0-1646108255708.png

 

This is how the overall Flow looks like.

 

VJR_1-1646108376430.png

 

 

Before running the Flow

 

VJR_2-1646108426092.png

 

 

After running the Flow for 2 decimal places

 

VJR_3-1646108486603.png

 

 

Simply change your inputs as per your needs in the Set variable statements.

 

Below is the script for the vbscript action.

 

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")

objWorkbook.Sheets("%SheetName%").Activate

If "%SingleDecimal%" = True then
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.0%%"
Else
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.00%%"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing

 

Alternatively:

Paste the below code in a blank flow editor, change the Excel path, set the values of the desired variables and run the flow

 

SET ExcelFilePath TO $'''C:\\test\\filename.xlsx'''
SET SheetName TO $'''Sheet1'''
SET FromRange TO $'''A1'''
SET ToRange TO $'''A3'''
SET SingleDecimal TO $'''False'''
Scripting.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFilePath%\")

objWorkbook.Sheets(\"%SheetName%\").Activate

If \"%SingleDecimal%\" = True then
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.0%%\"
Else
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.00%%\"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError

 

rickvvaldez
Regular Visitor

Thank you MichaelAnnis and VJR for all your help. 

The VB Script will be a great contribution to the community.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Kudoed Authors
Users online (2,795)