Str Function in VBA for Excel adds a character to the string - 【StackMirror】|string|casting|excel-vba|integer|vba

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

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

Easiest way to find out:

MsgBox(Asc(Right(Str(1),1)))

2012-04-04 02:30
by Ariel

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

2017-02-24 15:11
by fileinster