# 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) ## 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. ## 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

### 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

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.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. ## 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 #### Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

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

• Reply 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.

``````Sub Negative_Numbers()
Range("A1:A10").Select
Selection.NumberFormat = "0.00_);[Red](0.00)"
End Sub``````

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.  5 Excel Hacks You Never Knew  