Set a range to Text format

Posted by: karl.richardson on 13 August 2018, 6:48 pm EST

  • Posted 13 August 2018, 6:48 pm 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 13 August 2018, 9:48 pm 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 15 August 2018, 7:30 pm EST


    >>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,
  • Marked as Answer

    Replied 20 August 2018, 1:31 am EST


    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:

    Hope it helps.

    Best Regards,
Need extra support?

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

Learn More

Forum Channels