Excel VBA: Convert Number to Text with Format (A Total Guide)

While working with VBA in Excel, we often need to convert a number or a bunch of numbers to a text or a bunch of texts with the format. In this article, I’ll show you how you can convert a number to a text using VBA in Excel.

How to Convert Number to Text with Format in Excel VBA (Quick View)

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Convert Number to Text with Format in Excel VBA (2 Methods)

So, without further delay, let’s move to our main discussion. You can convert a number to a text with the format in VBA using 2 methods.

Method 1: Convert Number to Text with Format Using the VBA Format Function in Excel

First of all, you can convert a number to a text with a format using the Format function of VBA.

Enter the number along with the desired format inside the Format function of VBA.

For example, to convert the number 23.56377123 to a text of the format 23.56, the line of VBA code will be:

Text = Format(23.56377123, "0.00")

If you run this code with the line Msgbox Text, it’ll display the text of your desired format, 23.56.

Read More: How to Convert Numbers to Texts/Words in Excel

Method 2: Convert Number to Text with Format Using the TEXT Function of VBA

You can also convert a number to a text using the TEXT function of Excel. As this is an Excel function, you have to use the Application.WorksheetFunction method of VBA to use it in VBA.

For example, to convert the number 23.56377123 to a text of the format 23.56, the line of VBA code will be:

Text = Application.WorksheetFunction.Text(23.56377123, "0.00")

If you run this code with the line Msgbox Text, it’ll display the text of your desired format, 23.56.

Read More: Excel VBA to Convert Number to Text (4 Examples)

Examples to Convert Number to Text with Format in Excel VBA

We’ve learned how to convert a number to a text with the desired format with VBA in Excel. Now we’ll explore a few examples to accomplish this.

1. Developing a Macro to Convert a Range of Numbers to Texts with Specific Formats in Excel VBA

Here we’ve got a data set with the Sales Growth of some products over some years. The values are all fractions with numbers extended to 5 or 6 digits after the decimal.

Our objective is to develop a Macro to convert this range of fractions to texts with numbers extending to 2 places after the decimal.

The VBA code will be:

⧭ VBA Code:

Sub Convert_Range_of_Number_to_Texts()

Set Rng = ActiveSheet.Range("D4:G13")

Desired_Format = "0.00"

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        Rng.Cells(i, j) = Format(Rng.Cells(i, j), Desired_Format)
    Next j
Next i     

End Sub

⧭ Output:

Run this Macro. It’ll convert the range D4:G13 to text values with numbers extended to 2 places after the decimal.

Read More: How to Convert Text to Numbers in Excel

2. Creating a User Defined Function to Convert a Number or a Range of Numbers to Texts with Specific Formats in Excel VBA

We’ve seen how to create the Macro. Now we’ll develop a User-Defined function (UDF) to convert a number or a range of numbers to text (s) with a specific format.

The VBA code will be:

⧭ VBA Code:

Function Number_to_Text(Rng, Desired_Format)

If VarType(Rng) <> 8204 Then
    Output1 = Format(Rng, Desired_Format)
    Number_to_Text = Output1

Else

    Dim Output2() As Variant
    ReDim Output2(Rng.Rows.Count, Rng.Columns.Count)
    For i = 1 To Rng.Rows.Count
        For j = 1 To Rng.Columns.Count
            Output2(i - 1, j - 1) = Format(Rng.Cells(i, j), Desired_Format)
        Next j
    Next i
    Number_to_Text = Output2

End If

End Function

⧭ Output:

If you run the function with a single number and a format, it’ll return the number converting it to the text with the desired format.

For example, the formula:

Will return 23.56.

Again, if you insert a range of numbers, it’ll convert the whole range to the desired format.

It’ll return the whole range B3:D12 converting it to texts with 2 values after the decimal.

Read More: How to Convert Number to Text with Green Triangle in Excel

Similar Readings:

• How to Convert Date to Text YYYYMMDD (3 Quick Ways)
• Convert Number to Text for VLOOKUP in Excel (2 Ways)
• How to Convert Date to Text Month in Excel (8 Quick Ways)

3. Developing a UserForm to Convert a Range of Numbers to Texts with Specific Formats in Excel VBA

Finally, we’ll develop a UserForm to convert a range of numbers to texts with the desired format with VBA.

⧪ Step 1: Opening the UserForm

Go to the Insert > UserForm option in the VBA editor to open a new UserForm. A new UserForm called UserForm1 will be opened.

⧪ Step 2: Dragging Tools to the UserForm

Besides the UserForm, you’ll get the Toolbox. Move your cursor over the Toolbox and drag 4 Labels and 2 ListBoxes, and 2 TextBoxes in a rectangular shape over the UserForm.

Finally, drag a CommandButton to the bottom right corner.

⧪ Step 3: Writing Code for ListBox2

Double click on ListBox2. A Private Subprocedure called ListBox2_Click will open. Enter the following code there.

Private Sub ListBox2_Click()

If UserForm1.ListBox2.Selected(1) = True Then
    UserForm1.Label4.Visible = True
    UserForm1.TextBox2.Visible = True
End If

End Sub

⧪ Step 4: Writing Code for TextBox1

Then double click on TextBox1. Another Private Subprocedure called TextBox1_Change will open. Enter the following code there.

Private Sub TextBox1_Change()

On Error GoTo TB1

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 240

End Sub

⧪ Step 5: Writing Code for TextBox2

Then double click on TextBox2. Another Private Subprocedure called TextBox2_Change will open. Enter the following code there.

Private Sub TextBox2_Change()

On Error GoTo TB2

Set Rng1 = ActiveSheet.Range(UserForm1.TextBox1.Text)
Set Rng2 = ActiveSheet.Range(UserForm1.TextBox2.Text)

Rng2.Range(Cells(1, 1), Cells(Rng1.Rows.Count, Rng1.Columns.Count)).Select

Exit Sub

TB2:
    x = 240

End Sub

⧪ Step 6: Writing Code for CommandButton1

Finally, double click on CommandButton1. A Private Subprocedure called CommandButton1_Click will open. Enter the following code there.

Private Sub CommandButton1_Click()

Set Rng1 = ActiveSheet.Range(UserForm1.TextBox1.Text)

Desired_Format = ""

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Desired_Format = UserForm1.ListBox1.List(i)
    End If
Next i

If Desired_Format = "" Then
    MsgBox "Choose One Format.", vbExclamation
    Exit Sub
End If

For i = 1 To Rng1.Rows.Count
    For j = 1 To Rng1.Columns.Count
        If UserForm1.ListBox2.Selected(0) = True Then
            Rng1.Cells(i, j) = Format(Rng.Cells(i, j), Desired_Format)
        ElseIf UserForm1.ListBox2.Selected(1) = True Then
            Set Rng2 = ActiveSheet.Range(UserForm1.TextBox2.Text)
            Rng2.Cells(i, j) = Format(Rng1.Cells(i, j), Desired_Format)
        Else
            MsgBox "Choose One from Original Location and New Location. ", vbExclamation
        End If
    Next j
Next i

Unload UserForm1

Exit Sub

CB1:
    MsgBox "Enter a Valid Cell Reference in the Text Boxes.", vbExclamation

End Sub

⧪ Step 7: Writing Code for Running the UserForm

Insert a new Module from the VBA toolbar and insert the following code there.

Sub Run_UserForm()

UserForm1.Caption = "Convert Number to Text"

UserForm1.Label1.Caption = "Range: "
UserForm1.Label2.Caption = "Format: "
UserForm1.Label3.Caption = "Convert At: "
UserForm1.Label4.Caption = "New Location (First Cell): "

UserForm1.CommandButton1.Caption = "OK"

UserForm1.Label4.Visible = False

UserForm1.TextBox2.Visible = False

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle

UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox2.ListStyle = fmListStyleOption

UserForm1.TextBox1.Text = Selection.Address

UserForm1.ListBox1.AddItem "0"
UserForm1.ListBox1.AddItem "0.0"
UserForm1.ListBox1.AddItem "0.00"
UserForm1.ListBox1.AddItem "0.000"
UserForm1.ListBox1.AddItem "0.0000"
UserForm1.ListBox1.AddItem "0.00000"
UserForm1.ListBox1.AddItem "0.000000"
UserForm1.ListBox1.AddItem "0.0000000"
UserForm1.ListBox1.AddItem "0.00000000"
UserForm1.ListBox1.AddItem "0.000000000"
UserForm1.ListBox1.AddItem "0.0000000000"

UserForm1.ListBox2.AddItem "Original Location"
UserForm1.ListBox2.AddItem "New Location"

Load UserForm1

UserForm1.Show

End Sub

⧪ Step 8: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Select the data set from the worksheet (B3:D12 here) and run the Macro called Run_UserForm.

The UserForm will be loaded in the worksheet.

The selected range of cells will be shown in the Range box. Change it if you want to change.

Choose the desired format from the Format box.

Choose one between Original Location and New Location. If you choose New Location, then enter the New Location.

Then click OK. You’ll get the numbers from the selected range converted to text (s) with the desired format in the desired location.

Related Content: How to Convert Number to Text with Commas in Excel (3 Easy Methods)

Conclusion

So, these are the ways to convert a number to a text value with the desired format using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)
• Convert Number to Text without Scientific Notation in Excel
• How to Convert Number to Text and Keep Trailing Zeros in Excel (4 Ways)