In Microsoft Excel, If you have a column of numbers and you need to convert them all to text, you would select the column, then go to Format > Cells, and on the ‘Number’ tab, change the formatting to ‘Text’. That doesn’t quite do it though, the formatted cells will still work as numbers, which you can verify by using them in formulas.
To convert them to real text, You need to press [F2] and then hit [Enter], for each and every cell. Here’s a simpler way to convert a whole bunch of numbers to text. Let’s assume you have numbers in cells A2:A1000. Insert a temporary blank column B. In cell B2, enter this formula:
=TEXT(A2,”0”)
-
Copy the formula in B2 down to B3:B1000. We need to change these formula to values in order to have them become text.
-
Highlight cells B2:B1000. Use [Ctrl]+[C] to Copy, then Edit > Paste Special > Values > OK.
-
The entries in column B will now be text versions of the numbers in column A.
-
Now, copy column B back to Column A.
-
Delete the temporary Column A.
The key to this technique is the =TEXT() function. The second parameter describes how the number should be formatted before being converted to text. You may need to adjust this based on 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 always keep only the decimals as entered, use =TEXT(A2,”General”). This function is also useful for converting dates to formatted dates. Assuming you’ve set Windows to use the Indian system for dates, if you have ‘29/10/2009’ in cell A2, then using =TEXT(A2,”d mmmm, yyyy”) will give you ‘29 Oct 2009’.
I have been killing myself formating the cells to text then using [F2] then [Down Arrow] for thousands of items. This is awesome. Thanks for the help.
how to convert number to words in microsoft Excel
how the total in convert the text formate