Format Method - how do I stop a cell turning into a date?

[ChrisMattock]

Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.

Please Login or Register  to view this content.

I tried this... amoungst other things...

Please Login or Register  to view this content.

Can't seem to get anything to work, bugging the hell outta me! Appreciate any tips.

EDIT:
Additionally I need a custom number format that works like 0.##, i.e. displays up to 2 decimal places, but I don't want the decimal point visible if the zeroes aren't present - i.e. that currently displays "1" as "1."

[dominicb]

Good evening ChrisMattock

Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.

Hmmmm, sure is a pesky problem. The Format function doesn't always do a sterling job in forcing a cells number format - especially if Excel has other ideas

You could try forcing the cell format, then post the number to it, thus :

Please Login or Register  to view this content.

HTH

DominicB

[ChrisMattock]

Wow, I really thought that this would be a simple problem to solve! Obviously not, eventually after much furrowing of the brow I cam up with this, which works but I guess isn't ideal!

Please Login or Register  to view this content.

Sadly the below still aren't formatting as they should, I shall try the method you have provided.

Please Login or Register  to view this content.

EDIT:

Thanks Dominicb - problem solved in it's entirety! +rep for you (if I can!)