Set a range to Text format

Posted by: karl.richardson on 14 August 2018, 3:48 am EST

  • Posted 14 August 2018, 3:48 am EST

    I have a column in my spreadsheet which contains text which may or may not contain a number and the length of the text is not known. We do not want excel to convert this to a number in those instances and strip leading zeros, so I need to set the formatting number category to "Text" as you can in excel. How can I do that? I can only seem to set a custom format, which doesn't give me what I need.
  • Replied 14 August 2018, 6:48 am EST

    I have figured out that @ is the same as text, so thats fine. However,is there any way I can stop it stripping the zeros when I set the value of the range? Even if the cell is set as text, it still converts the string to a number when I set the value, so setting the number format to text to useless.
  • Replied 16 August 2018, 4:30 am EST

    Hello,

    >>However,is there any way I can stop it stripping the zeros when I set the value of the range?
    I could observe that on setting the NumberFormat of a range to "Text" in GcExcel, the set values do miss the leading zeros and hence, I am escalating this issue to the concerned team [Internal Tracking ID: 339315].
    As of now, you can manually set numbers into these ranges as they retain the leading zeros on manual input of the values.

    Best Regards,
    Esha
  • Marked as Answer

    Replied 20 August 2018, 10:31 am EST

    Hello,

    There are two ways to fulfill your requirement:

    • Add a prefix "`" before content of text, like:
      worksheet.Range["A1"].Value = "`   123";

    • Turn off auto parse when setting range value:
      workBook.AutoParse = false;


    Documentation reference for AutoParse: http://help.grapecity.com/gcdocs/gcexcel/onlinehelp/webframe.html#GrapeCity.Documents.Excel~GrapeCity.Documents.Excel.Workbook~AutoParse.html

    Hope it helps.

    Best Regards,
    Esha
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels