# How to Convert Numbers to Text in Excel (4 Methods)

### Method 1 – Using the TEXT Function

• The TEXT function in Excel can convert any value to text, including numbers. You can also specify a specific format for the text output.
• Examples:
• `TEXT(C5, 0)` ➡️ Converts to integer numbers.
• `TEXT(C6, "0")` ➡️ Same output as above.
• `TEXT(C7, "000000")` ➡️ Creates a 6-digit number with leading zeros if necessary.
• `TEXT(C8, "0000000")` ➡️ Creates a 7-digit number with leading zeros.

• You may need to add a leading zero or a currency, or you may need to convert the number into text with decimals.
• Below is a list of TEXT formulas and their output.

• TEXT(C5,0) >>> To get only integer numbers.
• TEXT(C6,”0″) >>> It will give the same output as above.
• TEXT(C7,”000000″) >>> This will give you a 6-digit number, if the digits are less than 6-digit then it will add the leading zeros to make it a 6-digit number.
• TEXT(C8,”0000000″) >>> Same as the previous one but will add leading zeros to make 7-digit numbers.
• TEXT(C9,”0.0″) >>> This will include one decimal place in text format.
• TEXT(C10,”0.00″) >>> Add two decimal places in text format.
• TEXT(C11,”0.000″) >>> Include three decimal places in text format.
• TEXT(C12,”\$#,###”) >>> This will give the currency format.
• TEXT(C13,”General”) >>> Use this to get scientific notation.
• TEXT(C14,”YYYY-MM-DD”) >>> Convert the number in date maintaining the YYYMMDD format.

Note: Excel keeps the number format on the right side of the cell and text format on the left. So, if the cell value alignment moved to the left, then the numeric values became text format.

### Method 2 – Adding an Apostrophe

• Prefixing a number with an apostrophe (), e.g., ‘123, converts it to text format.

### Method 3 – Using Format Cells

• Go to the Format Cells dialog box (press CTRL+1).
• Under the Number tab, choose the Text category and click OK.

• All selected numbers will be converted to text.

### Method 4 – Using Text to Columns Wizard

• Select the target numbers.
• Go to the Data tab and click on Text to Columns.

• Choose Delimited and proceed through the wizard.

• In the final step, select Text as the column data format and click Finish.

## How to Apply the Excel VLOOKUP Function to Avoid Mixing Different Formats

• The VLOOKUP function may not return the correct value when dealing with mixed text and number formats.
• For example, if your lookup value (defined ID) is in number format, but the lookup column (IDs) has a text format, direct use of VLOOKUP won’t yield the desired result.
• To address this, we need to convert the number to text format within the formula.
• Here’s the formula to achieve this:
`=VLOOKUP(TEXT(D17,0),B5:E14,3,FALSE)`

In this formula:

• TEXT(D17, 0) converts the number in cell D17 to text format.
• The VLOOKUP function then searches for this text-formatted value within the range B5:E14 and returns the corresponding value from the 3rd column (D column).

## How to Convert Numbers to Words with Excel VBA

• Suppose you want to convert numeric values (e.g., 1, 2) into their word equivalents (e.g., one, two).
• To achieve this, we’ll use Excel VBA (Visual Basic for Applications).
• Follow these steps:
• Open your worksheet.
• Under the Developer tab, click on Visual Basic to open the Visual Basic Editor.
• Insert a new module (from the Insert tab).
• Copy and paste the provided VBA code into the module.

``````Function Convert_Number_into_word_with_currency(ByVal whole_number)
Dim converted_into_dollar, converted_into_cent
my_ary = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
whole_number = Trim(Str(whole_number))
x_decimal = InStr(whole_number, ".")
If x_decimal > 0 Then
converted_into_cent = get_ten(Left(Mid(whole_number, x_decimal + 1) & "00", 2))
whole_number = Trim(Left(whole_number, x_decimal - 1))
End If
xIndex = 1
Do While whole_number <> ""
xHundred = ""
xValue = Right(whole_number, 3)
If Val(xValue) <> 0 Then
xValue = Right("000" & xValue, 3)
If Mid(xValue, 1, 1) <> "0" Then
xHundred = get_digit(Mid(xValue, 1, 1)) & " Hundred "
End If
If Mid(xValue, 2, 1) <> "0" Then
xHundred = xHundred & get_ten(Mid(xValue, 2))
Else
xHundred = xHundred & get_digit(Mid(xValue, 3))
End If
End If
If xHundred <> "" Then
converted_into_dollar = xHundred & my_ary(xIndex) & Dollar
End If
If Len(whole_number) > 3 Then
whole_number = Left(whole_number, Len(whole_number) - 3)
Else
whole_number = ""
End If
xIndex = xIndex + 1
Loop
Select Case converted_into_dollar
Case ""
converted_into_dollar = " Zero Dollar"
Case "One"
converted_into_dollar = " One Dollar"
Case Else
converted_into_dollar = converted_into_dollar & "Dollars"
End Select
Select Case converted_into_cent
Case ""
converted_into_cent = " and Zero Cent"
Case "One"
converted_into_cent = " and One Cent"
Case Else
converted_into_cent = " and " & converted_into_cent & "Cents"
End Select
Convert_Number_into_word_with_currency = converted_into_dollar & converted_into_cent
End Function
Function get_ten(pTens)
Dim my_output As String
my_output = ""
If Val(Left(pTens, 1)) = 1 Then
Select Case Val(pTens)
Case 10: my_output = "Ten"
Case 11: my_output = "Eleven"
Case 12: my_output = "Twelve"
Case 13: my_output = "Thirteen"
Case 14: my_output = "Fourteen"
Case 15: my_output = "Fifteen"
Case 16: my_output = "Sixteen"
Case 17: my_output = "Seventeen"
Case 18: my_output = "Eighteen"
Case 19: my_output = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(pTens, 1))
Case 2: my_output = "Twenty "
Case 3: my_output = "Thirty "
Case 4: my_output = "Forty "
Case 5: my_output = "Fifty "
Case 6: my_output = "Sixty "
Case 7: my_output = "Seventy "
Case 8: my_output = "Eighty "
Case 9: my_output = "Ninety "
Case Else
End Select
my_output = my_output & get_digit(Right(pTens, 1))
End If
get_ten = my_output
End Function
Function get_digit(pDigit)
Select Case Val(pDigit)
Case 1: get_digit = "One"
Case 2: get_digit = "Two"
Case 3: get_digit = "Three"
Case 4: get_digit = "Four"
Case 5: get_digit = "Five"
Case 6: get_digit = "Six"
Case 7: get_digit = "Seven"
Case 8: get_digit = "Eight"
Case 9: get_digit = "Nine"
Case Else: get_digit = ""
End Select
End Function``````

• Save the code.
• In cell D5, enter the created VBA function:
`=Convert_Number_into_word_with_currency(C5)`
• Press ENTER.

## Practice Section

We have provided a practice section on each sheet on the right side for your practice.

You can download the practice workbook from here:

## Frequently Asked Questions

### 1. How do I convert numbers to text in Excel without formatting?

• You can use the TEXT function to convert numbers to text without formatting. Let’s say you have a number in cell A1. Enter the following formula:

=TEXT(A1,0).

### 2. How to convert a formula to value in Excel without the Paste Special?

• To convert a formula to a value directly:
• Select the cell with the formula.
• Press F2 to edit the cell.
• Press F9 to evaluate the formula.
• Finally, press ENTER to replace the formula with the calculated value.

### 3. What is the fastest way to convert Numbers to Text in Excel?

• The quickest method is to add an apostrophe before the number. For example, ‘123 will be treated as text.

## Excel Convert Number to Text: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF