Excel: from Generic Format to Number Format

Help
,
1

Hello,
I have problems writing some values in Excel.
I saved the variable Premio in a String Variable and when I write this variable in an Excel cell, it is stored as Generic Format.
I would like to store it as a Number Format.
How can I perform this change?

image
image1600×900 58.1 KB

Thank you so much,
Camilla.

2

Hi @CamiCat,

Try this way
Use ` before the value.

“`”+yourvalue.ToString()
Regards,
Arivu

1 Like
3

Thank you @arivu96. I tried what you suggested but it’s unfortunately the same.
Can you help me again please? :slight_smile:

image
image1600×900 58 KB

4

Hi @CamiCat,

So you want 2.57952 right??? Without comma.

Else how you want???

Regards,
Arivu

5

image
image1600×900 58 KB

Sorry @arivu96. It is possible to save the cell in Numeric Format instead of Generic Format (The one highlighted in Blue in the image)?
Thank you so much for your help.
In this project they ask me to save the cell in Numeric Format :slight_smile:

6

Hi. So I’m understanding you are trying to get rid of the “green tick” mark on top left corner of cell cause it’s coming in as a string instead of a number?

How are you writing the table to Excel, a Workbook Read Range or an Excel Read Range in a Excel Scope? From my understanding, I believe Excel Scope will work best cause it allows Excel to use its automatic formatting which means it will see the number and format it as a number. So if you are not using an Excel Scope Read Range, then try that.

Regards.

1 Like
7

Oh sorry, I didn’t zoom in on your image. Additionally, your Value has both a decimal and a comma in the wrong place, so you need to replace the characters.

Of course, I use the format for numbers as being “2,579.52” so I don’t know if other countries are different. To fix it you would need to reformat the value so the comma and decimal are swapped.
“2.579,52”.Replace(“.”,“”).Replace(“,”,“.”)

But, I’m still a little confused as to how the number is supposed to be and am assuming you want “2579.52”

Regards.

1 Like
8

Hi, @ClaytonM.
Thank you very much for your precious help.
In my country, which is Italy, we use comma for decimals and the point for thousands.
The number is supposed to be 2579,52 as for the convention in my country.
I used Excel Application Scope with Write Range.
Can you please give me a help?
Thank you so much.
Camilla.

9

Hi @CamiCat,
Try this
Yourvalue.replace(",","").replace(".",",")

Regards,
Arivu

1 Like
10

If you were to double click in the cell then press Enter key, does it fix the cell?
The reason why I’m asking is I see this alot where the data writes to Excel as strings and the green tick mark shows up. EDIT: and it gets fixed by editing the cell manually

You could try using the Workbook Write Range to see if there is a difference.

Also, are you using a Build Data Table? I wonder if you have the column set as String.

There are probably non-ideal workarounds like using Select Range, Copy, then Clear All, and Paste on top again.

Other than that, we would probably need a sample file to reproduce the issue and possibly find a solution.

1 Like
11

Hi @ClaytonM,
if I double click in the cell then press Enter key, the cell fixs itself.
I used a Build DataTable with the column set to String.

So maybe I need to change the type to number, precisely decimal or I can convert the string to decimal with CDec()?
The value I’m trying to store in Number Format is taken from an Internet Page with Get Full Text.
Maybe there a possibility to save the value I get by screen scraping in a decimal format?

Thank you so much @ClaytonM.
Camilla

12

If the output has always the same format you can use an empty xlsx file as template and format the cells in the Premio column as Number. Then, if you’ll use Write Range or Write Cell inside an ExcelScope the values should be formatted as numbers. Using workbook activities doesn’t seems to work.

1 Like
13

Iam using Excel Application scope and the value “201810009198” is written to excel as “2.0181E+11”

Thanks in advance
Maria Josephina

1 Like
14

HI @ClaytonM

How can we remove the green tick marks in cells,where I am converting string to decimal,
where digits ending with zero also should have precision of two, while using build data table and setting up datatype as string only I am getting the precision of two digits and green tick above the cells,while changing the datatype as integer or decimal precision of two digits and green tick is not coming.

please refer the below screenshot

Capture2

OR can we set a custom format for a particular column in excel

as below mentioned screenshot

Capture4

Thanks & Regards,
A Manohar

1 Like
15

Hi.

There’s two ways you get the tick mark I think:

  1. where the value is correct, but the format was set to Text prior to putting in the value
    image
  2. where the value starts with a tick mark.
    image

If you were to fix this manually, you would need to click in the cell to update the value. So, you may need to do this with UiPath also. I believe, however, that a Write Range to a new sheet can do this all at once.
image

Then, lastly, you will need to format the columns. For example, invoice columns to “00000000” and amount columns to Number.

Typically, you can do this with a Select Range followed by an alt-key combination. For amount to Number format, here is an example:
image

Or if you need a custom format to include commas also, then it could be like this where excelSumFormat would = "#,##0.00":

image
image335×674 30 KB

It might also be beneficial to have an array of column names that identify the amount columns to be formatted. Then, you can calculate the range using this information for the Select Range.

Hopefully, that helps.

Regards.

2 Likes
16

This is my formatFormato

17

Hi @Beatriz_Eugenia_Duqu,

You can change the format using this activity …

check the video demo.

Thank you
Balamurugan.S

1 Like
18

I’m looking to see if this works.
Thanks

1 Like
20

It does not work, Help please