ExcelInsider Team
When working with Excel VBA, you will often need to convert numbers into text, whether it is for displaying values in a specific format, preparing data for concatenation, or manipulating cell contents as strings. Converting numbers to strings might seem simple, but VBA offers several methods that suit different needs and scenarios.
In this article, we will explore various ways to convert numbers to strings in Excel VBA, from the straightforward CStr function to more advanced formatting options and handling conversions in ranges. You will learn how to choose the right approach to write clean, efficient, and error-proof VBA code for your projects.
Steps to convert numbers to strings in Excel VBA using CStr and ensure Excel treats them as text:
➤ Press Alt + F11 , insert a new module, and paste this code:
Sub ConvertNumbers_UsingCStr_ForcedText()
Dim i As Long
Dim num As Variant
Dim result As String
' Loop through rows 2 to 11
For i = 2 To 11
num = Cells(i, 1).Value
result = CStr(num)
' Force Excel to treat the value as text
With Cells(i, 3)
.NumberFormat = "@"
.Value = "'" & result
End With
Next i
End Sub➤ CStr(expression) safely converts integers, decimals, and scientific numbers to readable string values.
➤ Excel may reformat stringified numbers as numeric unless you apply text formatting or use a leading apostrophe.
➤ The macro loops through rows 2–11, reads from column A, and outputs string values to column C.
➤ Add =ISTEXT(C2) in column D to verify the result is truly text.
➤ This approach ensures consistent string output, ideal for logging, exporting, or text-based processing.
Use CStr Function to Convert Numbers to Strings in Excel VBA
One of the most straightforward ways to convert a number to a string in VBA is using the CStr function. This built-in function handles various numeric types, integers, decimals, and negative numbers, and returns them as strings without changing the value itself.
For this demonstration, we’ll use a worksheet where column A contains different types of numbers (e.g., integers, decimals, scientific notation), and column C is where we want the string results to appear. We’ll loop through rows 2 to 11, apply CStr to each number in column A, and write the converted string into column C.
We have also added the ISTEXT function to the cells in column D to confirm if the numbers have been properly converted into strings. This method will help you see how CStr behaves across various number formats and why it’s a go-to method for basic number-to-string conversion.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ConvertNumbers_UsingCStr_ForcedText()
Dim i As Long
Dim num As Variant
Dim result As String
' Loop through rows 2 to 11
For i = 2 To 11
num = Cells(i, 1).Value
result = CStr(num)
' Force Excel to treat the value as text
With Cells(i, 3)
.NumberFormat = "@" ' Format as Text
.Value = "'" & result ' Add leading apostrophe to lock it as string
End With
Next i
End Sub 
➧ It automatically handles various data types like Integer, Double, and even Boolean.
➧ You can use it directly in formulas, loops, or assignments wherever you need the text form of a number.
➤ Return to Excel and press F5 to run the macro.
➤ Column C should now contain string versions of the numbers from column A. You can verify by checking the cell format or using ISTEXT function in a helper column.
Convert Numbers to Strings in VBA Using the Str Function
If you need a quick conversion from numbers to strings in Excel VBA, the Str function is another built-in option. Unlike CStr, it adds a leading space before positive numbers, which can be useful in some cases, mainly when formatting output that needs aligned signs or values.
For this example, we’ll continue using the dataset from column A (rows 2 to 11), and we’ll write the converted strings into column C, overwriting any previous content. This lets you compare how Str behaves compared to CStr, especially in how it formats positives and negatives.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ConvertNumbers_UsingStr()
Dim i As Long
Dim num As Variant
Dim result As String
' Loop through rows 2 to 11
For i = 2 To 11
num = Cells(i, 1).Value ' Read number from column A
result = Str(num) ' Convert to string using Str
Cells(i, 3).Value = result ' Write to column C
Next i
End Sub
➧ Negative numbers are shown with a minus sign, but without leading space.
➧ This function is useful for aligning text output, but you may need to Trim the result if the extra space is undesirable.
➤ Return to Excel and press F5 to run the macro.
➤ Check column C, positive numbers will now appear with a space at the start, which you can verify by inspecting the cell contents or comparing to results from the CStr method.
Format Numbers as Strings Using the Format Function in Excel VBA
Sometimes, converting a number to a string isn’t enough. You also want it to look a certain way. That’s where the Format function comes in. It lets you convert numbers to strings while controlling how the text appears, including decimal places, currency symbols, percentage signs, and more.
In this example, we’ll use the same dataset in column A and display the formatted string results in column C. We’ll format the numbers as currency with two decimal places so you can see how Format both converts and styles the result in one go.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ConvertNumbers_UsingFormat()
Dim i As Long
Dim num As Variant
Dim result As String
' Loop through rows 2 to 11
For i = 2 To 11
num = Cells(i, 1).Value
result = Format(num, "Currency") ' Convert and format as currency
Cells(i, 3).Value = result
Next i
End Sub 
➧ In this case, "Currency" applies your system’s currency symbol, commas for thousands, and two decimal places.
➧ You can swap "Currency" with other formats like "0.00", "#,##0", "0%", or custom date and time patterns.
➤ Return to Excel and press F5 to run the macro.
➤ Column C should now show each number as a currency-formatted string. Try changing the format string to explore different outputs like percentages or scientific notation.
Frequently Asked Questions
How do I convert an integer to a string in Excel VBA?
Use CStr(yourInteger), VBA’s built-in conversion function. It reliably turns integer values into text. If variable is already string-based or concatenated, implicit conversion may occur without errors.
Why does the Str function add a leading space in VBA?
Str(number) reserves a leading space for the sign of the number. Positive values start with a space, negative numbers include the minus sign. Use Trim to remove it.
When should I use CStr instead of Val or Str?
Use CStr for locale‑aware, explicit conversion to string. It handles different numeric types and respects regional settings, unlike Val or Str.
Can I concatenate a number with an empty string to convert it?
Yes, myString = myNumber & “” converts to string implicitly. But it’s less explicit than CStr, and may be confusing. CStr is clearer and preferred.
Wrapping Up
Converting numbers to strings in Excel VBA is easy once you know which method to use. CStr is the most straightforward and reliable for general conversions. Str also works but adds a leading space to positive numbers, which may require cleanup. When you need formatted output, like currency or percentages, Format is the best choice.
