Excel: from Generic Format to Number Format

excel
activities

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


Thank you so much,
Camilla.


#2

Hi @CamiCat,

Try this way
Use ` before the value.

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


#3

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


#4

Hi @CamiCat,

So you want 2.57952 right??? Without comma.

Else how you want???

Regards,
Arivu


#5


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.


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


#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


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


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