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

Question

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?

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
– Tony Dallimore, Commented Apr 4, 2012 at 8:44
And if you want to trim that extra blank space, you can always use this MsgBox (Len(Trim(Str(1)))) — Siddharth Rout
– Siddharth Rout, Commented Apr 4, 2012 at 10:27
Use Cstr(1) rather than Str(1). Cstr does not add a space. — HackSlash
– HackSlash, Commented Feb 24, 2021 at 23:57

Doug Glancy · Accepted Answer · 2012-04-04 02:37:32Z

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) = " "

answered Apr 4, 2012 at 2:37

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ariel · Accepted Answer · 2012-04-04 02:30:53Z

0

Easiest way to find out:

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

answered Apr 4, 2012 at 2:30

Ariel

4042 silver badges4 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:00:10Z

0

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

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Feb 24, 2017 at 15:11

fileinsert

4505 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related