how to preserve leading zero value when outputting range object to excel spreadsheet

Add-in Express™ Support Service
That's what is more important than anything else

how to preserve leading zero value when outputting range object to excel spreadsheet
 
Ming Chao




Posts: 24
Joined: 2019-01-23
Hi,

the app im working at needs to output a table of data to excel spreadsheet. one of the column contains mix of string and numeric values with leading zeros(ie, 000123). however excel trims all leading zero when outputting to the spreadsheet and causes these values malformed.

i found there are couple ways to handle this issue.
1. insert apostrophe(') before the text.
2. insert tab(\t) before the text.
3. format column as text then output the value.

1-2 seems to be a bad practice as they change the underlying data. 3 seems to be reasonable way handling numeric text.
is this the correct way to solve the problem? otherwise what is the best practice and how 'numeric text' should be handled?

thanks,
Posted 01 Oct, 2019 16:03:25 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Hello Ming,

I don't think there's a correct answer to this question; the question itself may only raised due to the architecture of Excel. At https://www.ablebits.com/office-addins-blog/2014/10/10/excel-convert-number-text/, we list options oriented on the end user. In addition to options #1 and #3, that article mentions the use of the TEXT built-in function. If #1-2 don't suit you, use #3. I'd probably use it myself.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 02 Oct, 2019 01:22:43 Top
Ming Chao




Posts: 24
Joined: 2019-01-23
thank you Andrei for your inputs. i believe #3 is the right way to take for our needs.
Posted 04 Oct, 2019 09:25:21 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 04 Oct, 2019 09:39:53 Top