The excel application scope change the data

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?

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.


1 Like