VBA Text

What is Excel VBA Text Function?

VBA Text is a part of the Worksheet function category. VBA Text function is a formatting function that can be used to convert any given numeric value into a specified number format. For example, if we have a date “19-05-2023,” then we can convert this date into “19-May-2023” by using the VBA text function. Look at the following data in Excel.

We can use the following code, which uses the VBA Text function converted to different formats.

Sub VBA_Text_Intro()

    Range(“B2”).Value = WorksheetFunction.Text(Range(“A2”).Value, “dd-mmm-yyyy”)

End Sub

When we execute this code, we will get the following result in cell B2. The VBA text function applied the new formatting to the given date, where we get the month in text and year in two-digit format.

Syntax of Excel VBA Text Function

As we already know, VBA Text is not a VBA function; instead, it is a worksheet function. Hence, we need to use the Worksheet function property to access the Text function. The following is the syntax of the VBA Text function.

WorksheetFunction.Text(Arg1, Arg2) As String

• Arg1: This is nothing but the value that we would like to format and convert to text.
• Arg2: In this argument, we will insert a desired formatting code that we need to apply to the given value in argument 1.

The result of the VBA Text function is a text value. The following are some of the codes for your reference.

Date	Code	Result
12-10-2023 15:15:28	dd-mm-yyyy	12-10-2023
12-10-2023 15:15:28	dd-mmm-yyyy	12-Oct-2023
12-10-2023 15:15:28	dd-mmmm-yyyy	12-October-2023
12-10-2023 15:15:28	dd-mmm-yyyy hh:mm:ss	12-Oct-2023 15:15:28
12-10-2023 15:15:28	hh:mm:ss	15:15:28

These are all just sample codes; there are plenty of those we can apply. Let us explore them.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use Excel VBA Text Function?

We will show you a step-by-step approach to applying the VBA Text function. Assume we have the value 45210. Let us apply the date format to this value and see how it pans out.

Step 1: Start the sub-procedure by naming the macro.

Step 2: Define a variable to hold the actual value.

Step 3: Assign the value 45210 to the declared variable.

Step 4: Define another variable to assign the result value.

Step 5: Let’s assign the VBA Text function to the result variable.

Step 6: The first argument of the VBA Text function is the variable that holds the actual value.

Step 7: The next argument requires the formatting code. Let us give the date formatting code.

We are applying the date format to the numerical value 45210.

Step 8: Next, show the converted value in a message box.

The following is the complete code for your reference.

Sub VBA_Text_Basic()

    Dim Actual_Value As Long

    Actual_Value = 45210

    Dim Result_Value As String

    Result_Value = WorksheetFunction.Text(Actual_Value, “dd-mm-yyyy”)

    MsgBox Result_Value

End Sub

Let us execute the code and see what we get in return.

The result looks interesting! Even though the original value was a numerical number, the moment we applied the date code with the formatting function text, it has converted the value to the date.

The reason is that Excel stores the dates in serial numbers. Hence, the actual value of 45210 is equal to the date, 11th Oct 2023. If we add one more number to the actual value, i.e., 45210+1 = 45211, the text function will show the next date, i.e., 12th Oct 2023.

Examples of Excel VBA Text Function

Let us show you a variety of examples to apply the VBA Text function.

Example #1 – Apply Time Formatting

Like dates, in Excel, time is also saved as a serial number starting from 0 to 1. Let’s assume we have a number 0.55. Let us try to apply the time format to this number.

We can use the following code to apply the time format.

Sub VBA_Text_Ex1()

    ‘Define a variable to assign the actual value

    Dim Actual_Value As String

    ‘Assign the value to the defined variable

    Actual_Value = 0.55

    ‘Define a variable to store the result of the Text function

    Dim Result_Value As String

    ‘Apply the VBA Text function

    Result_Value = WorksheetFunction.Text(Actual_Value, “hh:mm:ss”)

    ‘Showcase the result in a message box

    MsgBox Result_Value

End Sub

Let’s execute the code. We will get the following result in a message box. The value of 0.55 is equal to 12 minutes past 1 PM.

Example #2 – Format Date and Time Together

As we said earlier, both date and time are stored as serial numbers in Excel. Another interesting fact is that both date and time are stored together.

Let’s assume we have date number 45210 and time number 0.55. Once we add these two numbers, we will get the new number 45210.55. We must apply both date and time formats to this new number using the VBA Text function.

Look at the following code.

Sub VBA_Text_Ex2()

    ‘Define a variable to assign the date and time number

    Dim Date_Number As String

    ‘Assign the date and time value to the defined variable

    Date_Number = 45210.55

    ‘Define a variable to store the result of the Text function

    Dim Result_Value As String

    ‘Apply the VBA Text function

    Result_Value = WorksheetFunction.Text(Date_Number, “dd-mm-yyyy hh:mm:ss AM/PM”)

    ‘Showcase the result in a message box

    MsgBox Result_Value

End Sub

We have applied the time and date formatting code inside the VBA Text function, i.e.,

“dd-mm-yyyy hh:mm:ss AM/PM.” Let’s execute the code, and we will get the date and time value together.

Here, we have the new date and time, thanks to VBA Text, which applies the date and time formatting to the serial numbers.

Apply Formatting to Cells

This case study is a perfect example of making use of the VBA Text function in real-world scenarios.

For example, look at the following data in the Excel worksheet.

Assume we must concatenate these two columns and create a new column called “Print Line” in column C.

For example, in cell A2, we have an employee named “Peter Miller,” and his joining date in cell B2 is 02-09-2023. In cell C2, we need to create a concatenation of these two cells and create a test line as follows.

“Peter Miller Joined on 02-09-2023”

And the same goes for other employees as well. First, let’s try it for cells A2 and B2 in VBA code.

The following is the code to concatenate two cells into one.

Sub VBA_Text_Case_Study()

    Range(“C2”).Value = Range(“A2″).Value & ” ” & Range(“B2”).Value

End Sub

When we execute the code, we will get the following result in cell C2.

When we concatenate the text field with the date fields, the new result will not retain the actual date format. Instead, it takes the actual serial number behind the date. For example, the date 02-09-2023 is equal to the serial number 45171. Hence, the concatenation shows only the serial number in the new result cell.

To overcome this limitation, we must use the VBA Text function. Since we have multiple cells to combat, we will use the FOR NEXT loop to go through all the cells without duplicating the code again and again.

Sub VBA_Text_Case_Study()

    ‘Define a variable to find the last use row to make the code dynamic.

    Dim LR As Long

    ‘Find the last used row

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    ‘Define a variable to initiate the loop

    Dim k As Long

    ‘Initiate the loop

    For k = 2 To LR

        ‘Insert a new value in column 3

        ‘Use the VBA Text function to apply the date format

        Cells(k, 3).Value = Cells(k, 1).Value & ” ” & WorksheetFunction.Text(Cells(k, 2).Value, “dd-mm-yyyy”)

    Next k

End Sub

Once we execute the code, we will get the following result in column “C”.

As we can see in column “C,” we have the date concatenated with the text value. Here, the VBA Text function applied the date format to the date and converted it to text value; hence, we can concatenate the date with the text value. The eventual result is in text format.

Other Important VBA Text Usage

VBA text to columns: When we import the data from CVS or a text file, all the columns are combined with a common delimiter. To separate them into different columns, we can use the VBA text-to-columns method by specifying the common delimiter.

VBA text wrap: To wrap the cell text, we must use the “WrapText” property of either the VBA RANGE object VBA Cells Property or the Selection object. The following is the code to wrap the text in cell A1.

Range(“A1”).WrapText = True

Range(“A1”).WrapText = True

VBA text color: Similarly, to change the color of the text, we need to use the FONT property of the Range object. The following code will change the color of the text in cell A1 to vbBlue.

Important Things to Note

• VBA text is a worksheet function, not a VBA function. In Excel, dates are stored as serial numbers, and time is stored as decimals from 0 to 0.999.
• VBA Text function result should be assigned to either a variable or any cell value.
• If AM or PM is not specified, then the formatting will be 24 hours.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Text. with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Text. Here we explain the How to Use Excel VBA Text Function using syntax along with examples & downloadable excel template. You can learn more from the following articles –

• VBA StrComp
• VBA Substring
• VBA StrConv