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:

`=Number_to_Text(23.56377123,"0.00")`

Will return **23.56.**

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

`=Number_to_Text(B3:D12,"0.00")`

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.

This article would have been useful if it included formatting a negative number showing parenthesis instead of dash.

Hello,

JHORDISTA!Thanks for your comment. To format a negative number showing parenthesis, you can add this block of code with any of the above VBA code.

Hope this will help you!

Good Luck!

Regards,

Sabrina Ayon.Author, ExcelDemy