0

I am trying a lot to include the leading 0 in the excel which is getting trimmed from the excel sheet. I used like below for the column value and it is showing the leading 0, but with a single quote at the begining.

The value i am using like "'$value"

Can anyone please help me in this how to remove this single quote from the excel with having the leading 0?

user1755949
  • 93
  • 1
  • 12
  • Code please. (a) What kind of file format are you dealing with in Excel - csv or xls/xlsx? (b) What kind of data are you dealing with? Same number of digits or variable? – G42 Feb 05 '16 at 10:10
  • The file format is xlsx.It is string format data with 10 digits.But its trimmimg the leading 0 from it. – user1755949 Feb 05 '16 at 10:11
  • There are lots of questions already on SO about this. Please see http://stackoverflow.com/q/3992541/4606130 for example. Search "excel leading zero" and you will see what I mean. Please Search before you post. – micstr Feb 05 '16 at 10:25
  • I got the answer.Thanks for the reply. :) – user1755949 Feb 05 '16 at 10:29

1 Answers1

0

I would recommend formatting the data range as a 10 digit number instead of inserting an '.

You can find out more about Range.NumberFormat here on MSDN.

Where $value is your value and $worksheet is a Worksheet object: 

$value = "0000000001"
$workSheet.Range("A1").Value2 = $value
$workSheet.Range("A1").NumberFormat = "0000000000"

Output in Excel

G42
  • 9,791
  • 2
  • 19
  • 34