CODEWITHSUNDEEP
vbastringtype-conversioninteger
HelloWorld1
HelloWorld1

Reputation: 14100

How Do I Convert an Integer to a String in Excel VBA?

How do I convert the integer value "45" into the string value "45" in Excel VBA?

Upvotes: 188

Views: 1107507

Answers (11)

Brian
Brian

Reputation: 7146

Try the CStr() function

Dim myVal as String
Dim myNum as Integer

myVal = "My number is:"
myVal = myVal & CStr(myNum)

Upvotes: 102

Dave Green
Dave Green

Reputation: 11

It might be worth adding a trim to your conversion too, as integers convert with a leading space for inferred (positive sign) and applied (negative sign) values.

For comparison:

1/

lsDogsAge = "Barker is " & Cstr(liAgeInteger) & "years old"

2/

lsDogsAge = "Barker is " & Trim(Cstr(liAgeInteger)) & "years old"

1/ Barker is__7

2/ Barker is_7

Ironically this forum's software automatically truncates double spaces, so I've use underscores

Upvotes: 0

Gajendra Santosh
Gajendra Santosh

Reputation: 159

enter image description here

    Sub NumToText(ByRef sRng As String, Optional ByVal WS As Worksheet)
    '---Converting visible range form Numbers to Text
        Dim Temp As Double
        Dim vRng As Range
        Dim Cel As Object

        If WS Is Nothing Then Set WS = ActiveSheet
            Set vRng = WS.Range(sRng).SpecialCells(xlCellTypeVisible)
            For Each Cel In vRng
                If Not IsEmpty(Cel.Value) And IsNumeric(Cel.Value) Then
                    Temp = Cel.Value
                    Cel.ClearContents
                    Cel.NumberFormat = "@"
                    Cel.Value = CStr(Temp)
                End If
            Next Cel
    End Sub


    Sub Macro1()
        Call NumToText("A2:A100", ActiveSheet)
    End Sub

Reffer: MrExcel.com – Convert numbers to text with VBA

Upvotes: 2

Mahhdy
Mahhdy

Reputation: 592

The accepted answer is good for smaller numbers, most importantly while you are taking data from excel sheets. as the bigger numbers will automatically converted to scientific numbers i.e. e+10.
So I think this will give you more general answer. I didn't check if it have any downfall or not. 

CStr(CDbl(#yourNumber#))

this will work for e+ converted numbers! as the just CStr(7.7685099559e+11) will be shown as "7.7685099559e+11" not as expected: "776850995590" So I rather to say my answer will be more generic result.

Regards, M

Upvotes: 1

LimaNightHawk
LimaNightHawk

Reputation: 7083

Most times, you won't need to "convert"; VBA will do safe implicit type conversion for you, without the use of converters like CStr.

The below code works without any issues, because the variable is of Type String, and implicit type conversion is done for you automatically!

Dim myVal As String
Dim myNum As Integer

myVal = "My number is: "
myVal = myVal & myNum

Result:

"My number is: 0"

You don't even have to get that fancy, this works too:

Dim myString as String
myString = 77

"77"

The only time you WILL need to convert is when the variable Type is ambiguous (e.g., Type Variant, or a Cell's Value (which is Variant)).

Even then, you won't have to use CStr function if you're compounding with another String variable or constant. Like this:

Sheet1.Range("A1").Value = "My favorite number is " & 7

"My favorite number is 7"

So, really, the only rare case is when you really want to store an integer value, into a variant or Cell value, when not also compounding with another string (which is a pretty rare side case, I might add):

Dim i as Integer
i = 7
Sheet1.Range("A1").Value = i

7

Dim i as Integer
i = 7
Sheet1.Range("A1").Value = CStr(i)

"7"

Upvotes: 57

Slai
Slai

Reputation: 22876

The shortest way without declaring the variable is with Type Hints :

s$ =  123   ' s = "123"
i% = "123"  ' i =  123

This will not compile with Option Explicit. The types will not be Variant but String and Integer

Upvotes: 3

user2648008
user2648008

Reputation: 152

If you have a valid integer value and your requirement is to compare values, you can simply go ahead with the comparison as seen below.

Sub t()

Dim i As Integer
Dim s  As String

' pass
i = 65
s = "65"
If i = s Then
MsgBox i
End If

' fail - Type Mismatch
i = 65
s = "A"
If i = s Then
MsgBox i
End If
End Sub

Upvotes: 0

Yosem
Yosem

Reputation: 4765

CStr(45) is all you need (the Convert String function)

Upvotes: 294

Jonathan
Jonathan

Reputation: 1

Another way to do it is to splice two parsed sections of the numerical value together:

Cells(RowNum, ColumnNum).Value = Mid(varNumber,1,1) & Mid(varNumber,2,Len(varNumber))

I have found better success with this than CStr() because CStr() doesn't seem to convert decimal numbers that came from variants in my experience.

Upvotes: 0

Julian Kuchlbauer
Julian Kuchlbauer

Reputation: 895

In my case, the function CString was not found. But adding an empty string to the value works, too.

Dim Test As Integer, Test2 As Variant
Test = 10
Test2 = Test & ""
//Test2 is now "10" not 10

Upvotes: 7

dolphus333
dolphus333

Reputation: 1282

If the string you're pulling in happens to be a hex number such as E01, then Excel will translate it as 0 even if you use the CStr function, and even if you first deposit it in a String variable type. One way around the issue is to append ' to the beginning of the value.

For example, when pulling values out of a Word table, and bringing them to Excel:

strWr = "'" & WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)

Upvotes: 3

Related Questions