How to convert number to text in Excel - 4 quick ways

This tutorial shows how to convert numbers to text in Excel 2016, 2013, and 2010. Learn how to do this using Excel's TEXT function and use numbers to strings to specify the format. Learn how to change the format of numbers to text using the Format Cell… and Text to Column options.

If you use an Excel spreadsheet to store long or short numbers, you may want to convert them to text one day. There may be different reasons to change the number stored as a number to text. Here are the reasons why you might need to have Excel treat the entered numbers as text instead of numbers:

• Search by part rather than the entire number. For example, you might want to find all numbers containing 50, such as 501, 1500, 1950, etc.
• You may need to use the VLOOKUP or MATCH function to match two cells. However, if these cells are of different formatting, Excel will not treat the same value as a match. For example, A1 is formatted as text, while B1 is formatted as numeric 0. The leading zeros in B2 are custom formats. When these two cells are matched, Excel ignores leading 0s and does not show that the two cells are the same. Therefore, their formats should be uniform.

The same problem may also occur if the cell format is zip code, social security number, phone number, currency, etc.

Notice. If you want to convert numbers into text, such as amounts to text, this is a different task. Please check out the article about spelling numbers, titled "Two Best Ways to Convert Numbers to Text in Excel."

In this article, I'll show you how to convert numbers to text using Excel's TEXT function. If you're not so used to using formulas, check out the section I explained how to use the standard Excel Format Cells window, add apostrophes, and use the Text to Column wizard to change numbers to text formatting.

convert-number-to-text-excel-TEXT-function

- Convert numbers to text using Excel's TEXT function - Convert numbers to text in Excel using the Format Cell option - Add apostrophe to change numbers to text format - Convert numbers to text in Excel using the Text to Column wizard

### Convert numbers to text using Excel's TEXT function

The most powerful and flexible way to convert numbers into text is to use the TEXT function. It converts a numeric value into text and allows specifying how the value is displayed. This is helpful when you need to display numbers in a more readable format, or if you want to combine numbers with text or symbols. The TEXT function converts numeric values ​​into formatted text, so the result cannot be calculated.

If you are familiar with using formulas in Excel, using the TEXT function will not pose a problem for you.

1. Add a secondary column next to the column containing the number to be formatted. In my example it is column D.
2. Enter the formula =TEXT(C2,"0") in cell D2. In the formula, C2 is the address of the first cell containing the number to be converted.
3. Use the fill handle to copy the formula into the entire column.

4. After applying the formula, you will see that the alignment in the auxiliary column changes to left alignment.

5. Now you need to convert the formula in the auxiliary column to a value. Start by selecting the column.
6. Copy using Ctrl C. Then press the Ctrl Alt V shortcut to display the Paste Special dialog box.
7. In the Paste Special dialog box, select the Value radio button in the Paste group.

You will see a small triangle in the upper left corner of each cell in the secondary column, which means that these entries are now the text version of the numbers in your primary column.

Now you can rename the secondary column and delete the original column, or copy the result to your primary column and delete the temporary column.

Notice. The second parameter in the Excel TEXT function shows how the number will be formatted before conversion. You may need to adjust to your numbers:

The result of =TEXT(123.25,"0") will be 123.

The result of =TEXT(123.25,"0.0") will be 123.3.

The result of =TEXT(123.25,"0.00") will be 123.25.

To keep only the decimals, use =TEXT(A2,"General") .

hint. Suppose you need to format the cash amount, but the format is not available. For example, you can't display the numbers as GBP (£) because you're using the built-in format of the English (US) version of Excel. The TEXT function will help you convert this number to pound if you enter it like this: =TEXT(A12,"£#,###,###.##") . Simply enter the format you want to use in quotes -> Insert the £ symbol by holding down Alt and pressing 0163 on the numeric keypad -> Enter #,##.## after the £ symbol to get the comma of the delimited group and use the period as the decimal point. The result is text!

Convert numbers to text in Excel using the Format Cell option

If you need to quickly change numbers to strings, use the "Format Cells..." option.

1. Select the range of values ​​you want to format as text.
2. Right-click them and select the "Format Cells..." option from the menu list.

hint. You can display the "Format Cell..." window by pressing the Ctrl 1 shortcut key.

3. In the Format Cells window, select Text under the Numbers tab and click OK.

You will see the alignment changed to left alignment, so the format will change to text. This option is great if you don't need to adjust how the numbers are displayed.

Add an apostrophe to change the number to text format

If there are only 2 to 3 cells in Excel that need to convert the number to a string, you can benefit from adding an apostrophe before the number. This will immediately change the number format to text.

Simply double-click the cell and enter an apostrophe before the numeric value.

You will see a small triangle in the corner of this cell. This is not the best way to batch convert numbers to text, but if you only need to change 2 to 3 cells, this is the fastest way.

Convert numbers to text in Excel using the Text to Column wizard

You might be surprised, but Excel's "Text to Column" option performs quite well in converting numbers into text. Follow the steps below to see how it works.

1. Select the column you want to convert numbers to strings in Excel.
2. Navigate to the Data tab and click the Text to Column icon.

3. Just click through steps 1 and 2. In the third step of the wizard, make sure you have selected the Text radio button.

4. Click the "Finish" button and you will immediately see your numbers become text.

I hope the tips and tricks in this article help you process numeric values ​​in Excel. Use Excel's TEXT function to convert numbers into strings to adjust how numbers are displayed, or use "Format Cell" and "Text to Column" for batch quick conversion. If there are only a few cells, add an apostrophe. If you have any additional or questions, please feel free to leave a message. I wish you happiness and success in Excel!

The above is the detailed content of How to convert number to text in Excel - 4 quick ways. For more information, please follow other related articles on the PHP Chinese website!