Now let’s see, what would happen if we change the output format again from leading zeros to time format. 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.
Step 6: Now run the code by pressing the F5 key or by clicking on the Play Button. Step 5: Now consider the Text function and select the range that we need to convert. Step 4: Select the Index function along by evaluating it. Here that range is from cell A1 to A5 along with Worksheet function. Step 3: Now select the range of cells which we need to convert. Let’s consider those cells be from B1 to B5. Step 2: Select the range of cells where we want to see the output. Step 1: In a module, write the subcategory of VBA Text as shown below. For this, we will enter some numbers in the excel sheet as shown below. There is another direct way to apply VBA Text. If we know the exact number by which we can get an exact date or time then that will be better. Output = WorksheetFunction.Text(Input1, "DD-MM-yyyy") Step 11: Now let’s change the input number as 12345 and changing the format of output in DD-MMM-YYYY as shown below. We will see another time if we choose some other number. We will get the message box with a message as 02:57:07 AM. Step 10: Now run the code by pressing the F5 key or by clicking on the Play Button. Step 9: Now use MsgBox to see what comes as Output.
Output = WorksheetFunction.Text(Input1, "hh:mm:ss AM/PM") 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. 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.
Now, this function will be used as Worksheet function as shown below. Step 6: Now in the other defined variable which is Output, we will use it for putting VBA Text. Suppose, if we consider a decimal number 0.123 and see what we would get.ĭim Input1 As String Dim Output As String This could be the number which we want to see or it could be any random number. Step 5: Now consider any random number which we need to convert into the standard format. And this would also be as String.ĭim Input1 As String Dim Output As String End Sub Step 4: Now define another variable where we will be getting the output. So, define the first variable as String where we will be giving the input as shown below. Step 3: As per the syntax of VBA Text, there are two arguments that we will need. Step 2: In the opened VBA Module, write the subprocedure of VBA Text as shown below. Step 1: Open a Module which is available in the Insert menu tab as shown below. We will be converting the text or numbers into the standard format in all the upcoming examples in different ways.įollow the below steps to use Text function in VBA.
You can download this VBA Text Excel Template here – VBA Text Excel Template VBA Text Function – Example #1