VBA Text

Excel VBA Text Function

VBA Text function is only used in VBA. It seems like it converts the numbers to text. But in reality, it converts numbers to any format like Time, Date or number range. For this, we need to allow this function to enable as Class.

If we see the argument of VBA Text, then we will see it consists of Arg1 and Arg2 as shown below.

• Arg1 – Here we will put the value which we want to convert in the standard format.
• Arg2 – And here we will select the format in which we want to see the output coming as.

Both the variables will be defined as String.

How to Use Excel VBA Text Function?

We will learn how to use a VBA Text function with a few examples in excel.

VBA Text Function – Example #1

We will be converting the text or numbers into the standard format in all the upcoming examples in different ways.

Follow the below steps to use Text function in VBA.

Step 1: Open a Module which is available in the Insert menu tab as shown below.

Step 2: In the opened VBA Module, write the subprocedure of VBA Text as shown below.

Code:

Sub VBA_Text1()

End Sub

Step 3: As per the syntax of VBA Text, there are two arguments that we will need. So, define the first variable as String where we will be giving the input as shown below.

Code:

Sub VBA_Text1()

Dim Input1 As String

End Sub

Step 4: Now define another variable where we will be getting the output. And this would also be as String.

Code:

Sub VBA_Text1()

Dim Input1 As String
Dim Output As String

End Sub

Step 5: Now consider any random number which we need to convert into the standard format. This could be the number which we want to see or it could be any random number. Suppose, if we consider a decimal number 0.123 and see what we would get.

Code:

Sub VBA_Text1()

Dim Input1 As String
Dim Output As String

Input1 = 0.123

End Sub

Step 6: Now in the other defined variable which is Output, we will use it for putting VBA Text. Now, this function will be used as Worksheet function as shown below.

Step 7: Select the VBA Text function from the inbuilt function list. Now as per the syntax we will use our variable and format in which we want to convert that as Arg1 standard format as in which we want to convert that selected number.

Code:

Sub VBA_Text1()

Dim Input1 As String
Dim Output As String

Input1 = 0.123
Output = WorksheetFunction.Text(

End Sub

Step 8: In the Arg1 we write the Input1 variable which has our number that we need to convert and Arg2 will be the format as HH:MM:SS AM/PM.

Code:

Sub VBA_Text1()

Dim Input1 As String
Dim Output As String

Input1 = 0.123
Output = WorksheetFunction.Text(Input1, "hh:mm:ss AM/PM")

End Sub

Step 9: Now use MsgBox to see what comes as Output.

Code:

Sub VBA_Text1()

Dim Input1 As String
Dim Output As String

Input1 = 0.123
Output = WorksheetFunction.Text(Input1, "hh:mm:ss AM/PM")

MsgBox Output

End Sub

Step 10: Now run the code by pressing the F5 key or by clicking on the Play Button. We will get the message box with a message as 02:57:07 AM.

We will see another time if we choose some other number.

Step 11: Now let’s change the input number as 12345 and changing the format of output in DD-MMM-YYYY as shown below.

Code:

Sub VBA_Text2()

Dim Input1 As String
Dim Output As String

Input1 = 12345
Output = WorksheetFunction.Text(Input1, "DD-MM-yyyy")

MsgBox Output

End Sub

Step 12: Now again run the code. We will get the date as 18 Oct 1933

If we know the exact number by which we can get an exact date or time then that will be better.

VBA Text Function – Example #2

There is another direct way to apply VBA Text. For this, we will enter some numbers in the excel sheet as shown below.

Follow the below steps to use Text function in VBA.

Step 1: In a module, write the subcategory of VBA Text as shown below.

Code:

Sub VBA_Text3()

End Sub

Step 2: Select the range of cells where we want to see the output. Let’s consider those cells be from B1 to B5.

Code:

Sub VBA_Text3()

Range("B1:B5").Value

End Sub

Step 3: Now select the range of cells which we need to convert. Here that range is from cell A1 to A5 along with Worksheet function.

Code:

Sub VBA_Text3()

Range("B1:B5").Value = Range("A1:A5").Worksheet.

End Sub

Step 4: Select the Index function along by evaluating it.

Code:

Sub VBA_Text3()

Range("B1:B5").Value = Range("A1:A5").Worksheet.Evaluate("INDEX(

End Sub

Step 5: Now consider the Text function and select the range that we need to convert. And the format be in multiple zeros.

Code:

Sub VBA_Text3()

Range("B1:B5").Value = Range("A1:A5").Worksheet.Evaluate("INDEX(TEXT(A1:A5,""'000000""),)")

End Sub

Step 6: Now run the code by pressing the F5 key or by clicking on the Play Button. We will see the numbers available in column A now got converted into text with multiple zeros which leads to 6 digit numbers in column B.

Now let’s see, what would happen if we change the output format again from leading zeros to time format.

Step 7: In Arg2, put time format as HH:MM:SS and change the output range cell to C1 to C5 without disturbing the output obtained from the previous code.

Code:

Sub VBA_Text3()

Range("C1:C5").Value = Range("C1:C5").Worksheet.Evaluate("INDEX(TEXT(A1:A5,""hh:mm:ss""),)")

End Sub

Step 8: Again run the code. We will see, column C will get filled with the default time format.

Let’s try another experiment.

Step 9: In this, we will change the output cell range from D1 to D5 where we will see the VBA Text. And change the Arg2, in Date format which is DD-MMM-YYYY as shown below.

Code:

Sub VBA_Text3()

Range("D1:D5").Value = Range("D1:D5").Worksheet.Evaluate("INDEX(TEXT(A1:A5,""DD-MMM-YYYY""),)")

End Sub

Step 10: Again run the code. This time we will see, the date will be seen in column D. And those dates are in the format that or DD-MMM-YYYY format.

This is how we can change the numbers in any standard format that we want.

Pros & Cons of Excel VBA Text Function

• This is easy to implement.
• We can change the numbers in any format that we want to see.
• We cannot convert any text into the required number format.

Things to Remember

• VBA Text converts only numbers into numbers. But the format of numbers will be the standard formats such as Date, Time, the combination of Date and Time or any digits sequence.
• If you are applying and changing the numbers into a format of preceding zeroes, then put the apostrophe before the zeros. So that numbers will get converted into text and the Zeros will appear.
• If we try to convert any text with VBA Text, then it will give the output as Text only.
• To get the exact output, we must know the combination of numbers which will provide the exact time and date that we want to see.
• Once done, always save the complete code in Macro-Enabled format, so that the code will not be lost.

Recommended Articles

This is a guide to VBA Text Function. Here we discuss how to use Text function in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA RGB
2. VBA XML
3. VBA Subscript out of Range
4. VBA IsError