Macro inadvertently changing numbers formatted as text to scientific formatting

Good afternoon. I'm using a macro that creates a hyperlink to our internal tracking system, using all the tracking numbers in column F. It turns all the tracking ID's into hyperlinks in their respective cells. (I did not come up with this code, I actually got the basis of it from a forum post on this site.) The tracking ID's are formatted as text, since excel doesn't like leading 0's, but they are necessary for tracking purposes. The macro works, except that the text displayed in the cell turns into scientific number formatting as soon as I run the macro. I've tried changing cl.Value to cl.Text after TextToDisplay, but it doesn't seem to work. Any ideas? (I am not including the actual url in the code since it's confidential/proprietary information. Thanks!

Please Login or Register  to view this content.

Thank you so much for taking the time to reply. I made sure the destination range (incidentally, the same range as I'm referencing, since I wanted the hyperlinks in the same cells) are formatted as text. I tried Cstr(cl.Value) but unfortunately, the column still ended up changing to scientific format when I ran the macro. There's probably something obvious I'm missing, but I'm not sure what else to try.

Again, I really appreciate the suggestion. I may end up just creating a new column and putting all the hyperlinks in that column with the text to display as "TRACK" or something similar.

Logically, it seems like your suggestions should work, but even with those changes to the code, the format still changed to scientific. I really appreciate your suggestions, in any case. If for some reason I figure it out, I'll make sure to update this thread.