Solved: Convert a number into a string with a leading zero

[Digita]

Hello VBAExpressers,

I need to convert a number to a string. VBA Cstr function would normally handle this chore. However, my case is a bit more complex. I want the converted string to have a leading zero eg: from 123456 (number) to 0123456 (string).

My first attempt to test the output (see the following script) doesn?t work out.

Msgbox IsNumeric(Cstr(?0? & 123456))

Any idea? Will appreciate your help.

Thanks in advance & regards


kp

[Ken Puls]

How about:

[vba]MsgBox "0" & CStr(123456)[/vba]

HTH,

[mikerickson]

[VBA]MsgBox Format(123456, "0000000")[/VBA]

[Digita]

Thanks guys for your quick responses. However, after adding the leading zero to a number I want to force VBA to think that it is a string (hence the Isnumeric test). Hope this would clarify my question.

[mikerickson]

Both posted codes return strings. Could you post some inputs values and desired results?

[Digita]

First of all, thanks so much for your patience mikerickson and my sincere apology for not making my question clearer. Because VBA automatically discards the zero in front of a number, I have to convert a number into a string and dim it as a string in a sub. This is where I get stuck.

If Cstr converts a number to a string, then the boolean test Isnumeric(Cstr(123456)) should return False. However, the result does not turn out that way when I run the following code. [VBA]MsgBox IsNumeric(Cstr(123456)) [/VBA]

Any idea why?

Thanks again.

Regards


kp

[mikerickson]

The string "123456" is a numeral, the string representation of a number. Hence, it is a numeric string.
Q.E.D. IsNumeric("123456") = True

[Bob Phillips]

Whereas =ISNUMBER(TEXT(1234,"@")) returns false. Where is the consistency in that?

[mikerickson]

"123456" is numeric, it is not a number.

The spreadsheet function ISNUMBER is not the identical equivalent of VB's IsNumeric.

IsNumeric(Format(1234, "@")) = True

[Bob Phillips]

"123456" is numeric, it is not a number.

That is semantics, is not a number numeric then?

The spreadsheet function ISNUMBER is not the identical equivalent of VB's IsNumeric.

Clearly it is not, but in what way should it be, why is it different. Your reasoning for IsNumeric returning true

The string "123456" is a numeral, the string representation of a number. Hence, it is a numeric string.
Q.E.D. IsNumeric("123456") = True

cannot be used if ISNUMBER doesn't do the same. TEXT(1234,"@") is just as much a string/text representation of a number as your VBA example.

I for one can think of no reasonable argument for them behaving differently. To me, both should examine a string to determine whether it is actually a number, obviously 1234 is a number, but is "1234"?

[mikerickson]

That is semantics, is not a number numeric then?

Yes, both the number 2 and the string "2" are numeric. Sytnax and sematics are often partners.

Clearly it is not, but in what way should it be, why is it different. Your reasoning for IsNumeric returning true

I'm not arguing that MicroSoft made wise decisions about how ISNUMBER and IsNumeric should act. I applaud their use of English to put memnotics into the function names that give a reminders of their functionality.

ISNUMBER returns TRUE when the argument is a number, but not a numeric string.
IsNumeric returns True when the argument is a numeric variant.

Making this small distinction helps me remember the limitations of both functions.

(For some reason, looking at the way MS treats IsNumeric makes data type Date clearer to me.)

[Bob Phillips]

Oh that MS were so rational in the way that they implement across the board. The truth is that it is all done by devolved teams and the cross-checking is lamentably lacking, so they implemented different things. If it were rationally determined, it again raises my question, what is the reason for determining that Excel and VBA need different functions?

[Digita]

Hi Guys,

123456" is numeric, it is not a number

Yeah, it is sometimes mind bogging to understand the logic in VBA programming

Really appreciate your responses.

Regards and thanks


kp