Str Function in VBA for Excel adds a character to the string

Go To StackoverFlow.com

4

I am converting an integer to string using the str() function

However, I noticed that the str() function would return an extra character to the string.

For example, MsgBox(Len(str(1))) would return 2.

What is the extra character being appended?

2012-04-04 02:27
by Steveng
Doug Glancy's quote is correct although I would have added the next line: "Use the Format function to convert numeric values you want formatted as dates, times, or currency or in other user-defined formats. Unlike Str, the Format function doesn't include a leading space for the sign of number." [My italics - Tony Dallimore 2012-04-04 08:44
And if you want to trim that extra blank space, you can always use this MsgBox (Len(Trim(Str(1))))Siddharth Rout 2012-04-04 10:27
Good points, both - Doug Glancy 2012-04-04 19:57


10

From Excel 2010 help:

"When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied."

And sure enough this statement returns True in the debug window:

? left(str(1),1) = " "
2012-04-04 02:37
by Doug Glancy
>
  • 1 Yup you are quite correct on that one :)
  • - Siddharth Rout 2012-04-04 10:29


    0

    Easiest way to find out:

    MsgBox(Asc(Right(Str(1),1)))
    
    2012-04-04 02:30
    by Ariel


    0

    As pointed out in this answer you should use the format() function.

    2017-02-24 15:11
    by fileinster