Set a range to Text format

Set a range to Text format

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

• 

  karl.richardson

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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.

• 

  karl.richardson

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 15 August 2018, 7:30 pm 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

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 20 August 2018, 1: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