The excel application scope change the data

excel
activities

#1

Numeros%20formato
When i use write range it take away the text format from the cell and i cant use the write range of the wokbook beacuse i am working with a xlsb file, anybody knows a way to write it as text?


#2

Hi, Excel does this by default. Like if you were to enter the numbers manually, it will do the same thing.

The solutions to this are one of the following, I think:

  • If the numbers are already zero-filled as strings, format column to text prior to writing data so they stay zero-filled
  • Keep the values as numbers but format the column with a Custom format, 0000, so it is zero-filled
  • Change the number to start with a single quote, ‘0025 or "’"+25.ToString(“0000”)
  • Use a formula to convert number to string, select range of column, and a Ctrl+d (to filldown)… then, copy/paste values ontop

Either way, the green ticks will show up though, unless you change the Excel options.

If there are other methods to zero-fill that don’t require many steps and is efficient, I’m also interested though, cause it’s a common trend in business and a challenge.

Typically, I just format the column. If it’s already zero-filled, just make sure column is set to text; if they are numbers not zero-filled, just change format to “0000”, but keep in mind in this case the values are not zero-filled.

Regards.


Read Range - Datatable converting the values in Number Format