locked
Prevent Excel truncating leading zeros for HYPERLINKS RRS feed

  • Question

  • I know we can prevent excel from truncating leading zeros by setting the cell format to text.

            public static void setRangeNumberFormat(Excel.Range range, string format)        { 

            range.NumberFormat = format;     

      }


    (Here format is "@")

    That works. However, when I add a hyperlink and in its text to display I add a value like 00111, the hyperlink name is truncated. 

            public static void setRangeHyperLink(Excel.Range range, string hyperlink,string friendlyName)
            {
                Excel.Hyperlink hyperLink = range.Hyperlinks.Add(range, hyperlink, "", hyperlink, friendlyName);     
                
            }

    (Here friendlyName would be "00111")

    Is there any way to prevent that? Shouldn't the formatting be applicable to hyperlinks as well?


    • Edited by Furqan1992 Monday, April 11, 2016 7:20 AM
    Monday, April 11, 2016 7:18 AM

Answers

  • Re:  formulas and text cell formatting

    A cell containing a working formula cannot be formatted as text (the formula would not work).
    Suggest you add a leading apostrophe to the friendly name. That character (39) is not displayed in Excel when it is the first character in the cell.
    ( or add a leading space )

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, April 11, 2016 3:16 PM
  • Hi George,

    Another simple solution for adding leading 0 in Excel is using the Text formula.  For example, if you want A1 shows 00111 instead of 111, we can set the formula like this:

    Range("A1").Formula = "=text(111,""00000"")"
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 12, 2016 8:18 AM

All replies

    1. Please elaborate how the hyperlink is "truncated"
    2. This is not VBA. What language are you trying to work with Excel? C/C++/C#?

    Best regards, George

    Monday, April 11, 2016 2:20 PM
  • Re:  formulas and text cell formatting

    A cell containing a working formula cannot be formatted as text (the formula would not work).
    Suggest you add a leading apostrophe to the friendly name. That character (39) is not displayed in Excel when it is the first character in the cell.
    ( or add a leading space )

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Monday, April 11, 2016 3:16 PM
  • Hi George,

    Another simple solution for adding leading 0 in Excel is using the Text formula.  For example, if you want A1 shows 00111 instead of 111, we can set the formula like this:

    Range("A1").Formula = "=text(111,""00000"")"
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 12, 2016 8:18 AM