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

Question

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

User · Accepted Answer

CStr 45  is all you need  the Convert String function

User · Answer

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

User · Answer

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        amp  WorksheetFunction Clean  cell iRow  iCol  Range Text

User · Answer

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

User · Answer

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  amp       Test2 is now  10  not 10

User · Answer

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

User · Answer

Try the CStr   function  Dim myVal as String  Dim myNum as Integer   myVal    My number is   myVal   myVal  amp  CStr myNum

User · Answer

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  amp  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    amp  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

User · Answer

Another way to do it is to splice two parsed sections of the numerical value together   Cells RowNum  ColumnNum  Value   Mid varNumber 1 1   amp  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

User · Answer

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

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

Examples related to vba

Examples related to string

Examples related to type-conversion

Examples related to integer