🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Str in Excel — The Leading-Space Trap and Why CStr Is Almost Always Right

|

VBA Str in Excel — The Leading-Space Trap and Why CStr Is Almost Always Right

TL;DR — Str turns a number into text, but it's an old BASIC function that hides two traps. First, Str(42) returns " 42" with a leading space (it reserves a column for the sign). Second, Str is locale-blind — it always uses a . decimal point even on a German machine that expects a comma. That makes Str great for writing locale-independent files and wrong for anything a user sees. The default for "number to text" is CStr (no space, respects locale) or Format (full control). Reach for Str only when you deliberately want a fixed . separator — and even then, most code shouldn't.

This is the companion to VBA CStr, CDate & Val, which covers turning text into numbers and dates. This article is the other direction: turning a number into text — and specifically why the obvious-looking Str function is the one you should usually avoid.

What you'll learn

  • The leading-space trap: why Str(42) is " 42" and what it breaks
  • Why Str ignores your regional settings — and when that's actually useful
  • Str vs CStr vs Format — which to use and when
  • The Str/Val pair vs the CStr/CDbl pair

The mental model: Str is a relic with two landmines

Think of Str as a tool from an older era of BASIC, built when numbers were printed in fixed-width columns. It still behaves that way: it leaves room for a minus sign, and it doesn't care what country you're in. Both behaviors made sense in 1985 and surprise people in 2026.

Sub TheTwoLandmines()
    Debug.Print "[" & Str(42) & "]"     ' -> "[ 42]"   leading space!
    Debug.Print "[" & Str(-42) & "]"    ' -> "[-42]"   sign fills the slot
    Debug.Print Str(3.14)               ' -> "3.14"   always a dot, any locale
End Sub

Landmine 1: the leading space

Str always reserves the first character for the sign. Positive numbers get a space there; negatives get the -. So Str(42) is three characters: space, 4, 2. This quietly breaks three things:

Sub LeadingSpaceBreaksThings()
    ' 1) Concatenation gets an extra space
    Debug.Print "Total: " & Str(42)     ' -> "Total:  42"   (two spaces)

    ' 2) Comparisons fail
    Debug.Print (Str(42) = "42")        ' -> False   (" 42" <> "42")

    ' 3) Written to a cell, it's text-with-a-space:
    '    looks like a number, but ISNUMBER is False and sorting breaks
    Range("A1").Value = Str(42)
End Sub

People "fix" this with Trim(Str(n)), which works but is a code smell — you're papering over the wrong function. The real fix is to use a function that never adds the space in the first place.

Landmine 2: Str is locale-blind

Str always emits a . as the decimal separator, regardless of Windows regional settings. On a machine configured for German (which uses , for decimals), Str(3.14) is still "3.14".

This is the one place Str earns its keep. When you're writing a CSV, SQL statement, JSON, or any machine-readable format, you want a fixed . separator so the file parses the same everywhere. Str gives you that for free — where CStr would emit 3,14 on a German box and corrupt your file.

But for anything a person reads, locale-blindness is a bug: a German user expects 3,14, and Str will show them 3.14.

The fix: CStr by default, Format for control

Sub TheRightTools()
    ' CStr — no leading space, respects the user's locale. The default.
    Debug.Print "[" & CStr(42) & "]"        ' -> "[42]"
    Debug.Print CStr(3.14)                  ' -> "3,14" on a German machine

    ' Format — full control over digits, padding, thousands separators
    Debug.Print Format(42, "0")             ' -> "42"
    Debug.Print Format(1234.5, "#,##0.00")  ' -> "1,234.50" (or locale equiv.)
    Debug.Print Format(7, "000")            ' -> "007"
End Sub

Here's the decision in one table you can keep in your head:

  • CStr — number → text for general use. No space, locale-aware. Your default.
  • Format — when you need padding, fixed decimals, or thousands separators.
  • Str — only when you specifically need a locale-independent . for a file or protocol, and you accept (or Trim) the leading space.

The judgment, stated plainly: in 2026, a bare Str() in new code is almost always either a bug or an un-commented file-writing special case. If you mean "give me this number as text," write CStr. If you mean "format this number," write Format. Save Str for the narrow locale-independent-output case, and put a comment next to it explaining why.

The Str/Val pair vs the CStr/CDbl pair

These functions come in matched, locale-aware-or-not pairs — mixing them is how round-trips silently corrupt:

  • Str ↔ Val — both locale-blind (always .). Val("3.14") is 3.14 on every machine; Val also stops at the first non-numeric character. Use this pair for fixed-format file I/O.
  • CStr ↔ CDbl — both locale-aware. CDbl respects the regional comma; CStr emits it. Use this pair for round-tripping values the user sees.

The bug to avoid is crossing the pairs: write with Str (dot) and read back with CDbl on a comma-locale machine and the number changes. Keep each round-trip within one pair.

How ExcelMaster helps

Number-to-text VBA usually exists to build an export or a label — a price tag, a CSV field, a padded invoice number. ExcelMaster generates that from a description — "export these amounts as text with two decimals and a leading-zero ID" — and gets the locale and padding right without you choosing between Str, CStr, and Format and discovering the leading space the hard way.

You'll still write conversions inline in macros. But for "produce this formatted export" work, describing the format beats hand-picking the conversion function.

Frequently asked questions

What is the difference between Str and CStr in VBA?

Str adds a leading space before positive numbers and always uses a . decimal separator regardless of locale. CStr adds no space and respects the user's regional settings. For general "number to text" conversion, use CStr; use Str only when you need a locale-independent . for a file or protocol.

Why does VBA Str put a space in front of the number?

Str reserves the first character for the sign — a space for positive numbers, a - for negatives. It's a holdover from fixed-width numeric printing. To remove it, either use CStr (which never adds the space) or wrap it as Trim(Str(n)), though CStr is the cleaner fix.

Does VBA Str respect the regional decimal separator?

No. Str always emits a . even on machines configured for a comma decimal (German, French, etc.). That's useful for writing CSV/SQL that must parse the same everywhere, but wrong for values shown to users — use CStr or Format for those.

How do I convert a number to a string in VBA?

Use CStr(number) as your default — no leading space and locale-aware. Use Format(number, "0.00") when you need specific decimals, padding, or thousands separators. Reserve Str(number) for locale-independent file output only.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-06-14.

Related guides: VBA CStr · VBA Format · VBA Concatenate · VBA UCase & LCase · VBA Trim