Converting Forced Text to Numbers
Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Converting Forced Text to Numbers.
When you enter information in a worksheet, Excel does its best to decipher what type of data you are entering. If your entry can be translated as a number or a date, then Excel treats it that way. You can overcome this natural tendency of Excel by formatting a cell as text before entering information in it. When you do, the information in the cell is always treated as text.
Of course, forcing Excel to treat your input as text can have unwanted repercussions later. For instance, you may decide that you want to add up the contents of cells that are formatted as text. If you use a formula such as the following, then Excel has no problem:
=A1 + A2
Excel provides the correct sum, provided at least one of the cells (A1 or A2) was not formatted as text. To make matters tricky, however, if you use the SUM function (which most people do when summing an entire column or row), then you won't get the proper sum. The SUM function ignores any cells formatted as text. How do you get around this?
It is possible to remove the text formatting attribute from the cells you want to sum, but that won't cause Excel to reassess the contents of the cells and treat them as numbers or dates, where appropriate. There are several different ways you can force the conversion of forced text into numeric values, ranging from macros to using formulas in other columns to perform the conversion. The following three solutions, however, seem to be the easiest and quickest.
First, select the cells that are formatted as text, but that actually contain numbers. You may see a small exclamation mark icon appear near the upper-left corner of the selected cells. (See Figure 1.)
Figure 1. The exclamation mark icon.
Click the icon and some options appear. You want to select the Convert to Number option. Excel immediately changes the format on the cells (to General) and converts the text values to numbers.
If you don't see the exclamation mark icon when you select the cells, then you can use a different method to do the conversion:
- Enter the value 1 in an empty cell.
- Select the cell and press Ctrl+C. The value is now in the Clipboard.
- Select the range of cells you want to convert to numbers.
- Display the Home tab of the ribbon.
- Click the down-arrow under the Paste tool and choose Paste Special from the resulting choices. Excel displays the Paste Special dialog box. (See Figure 2.)
Figure 2. The Paste Special dialog box.
- Make sure the Multiply radio button is selected.
- Click on OK.
This works because Excel multiples each cell in the range (step 3) by the value in the Clipboard and then again stores the value in the cell. Since any number multiplied by one is that same number, you effectively force Excel to replace the contents of the cell with the numerical equivalent of the text that was previously there.
You could also, if desired, select a blank cell in steps 1 and 2. When it comes to step 6, choose Add instead of Multiply. The result is that a zero value (what Excel assumes for a blank cell) is added to the target values and they are converted, again, to numbers.
If the range you want to convert contains only numbers formatted as text and not any actual text, then the following steps work well:
- Select the range of cells you want to convert to numbers.
- Display the Data tab of the ribbon.
- Click the Text to Columns tool in the Data Tools group. Excel displays the Convert Text to Columns Wizard. (See Figure 3.)
Figure 3. The Convert Text to Columns Wizard.
- Click on Finish.
If you try these three steps on a range of cells that has text containing spaces or tabs, it is possible that you could overwrite data in columns to the right of the selected range. That is why it is safest to use if the range only contains numeric values formatted as text.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10205) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Converting Forced Text to Numbers.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Leave your own comment:
Comments for this tip:
Marvella Jackson 09 Dec 2016, 15:27
Is there a way to format a cell that has text into a number and afterwards sum those number?
For example L in one cell
Next cell has 50, which represents L
And if I have two Ls it would calculate 100
Kevin Kweenarto 23 Oct 2016, 09:50
Thank you, the multiply tips work for me. Is the a way to enable that diamon exclamation mark? I seems to dismiss it the other day
suraj kumar 16 Jul 2016, 13:12
$ Sixty Six Million Three Hundred Sixty Four Thousand Three Hundred Thirty Four Dollars and Twenty Two Cents AND Thirty %
Dinesh Singh Chauhan 08 Jun 2016, 23:50
It is excellent site to get useful tips.
Neren 08 Jun 2016, 07:07
Hi ... i am having difficulty with what you mentioned above. i have used the =ISTEXT formula and it confirms that the value is a text but no error icon appears for me to click Convert to Number option. Also the value in the cells are left aligned confirming it is text. I have used the the Text to Column wizard, also used paste special to ADD 0 or multiply as seen on youtube videos but nothing seems to work. How do i solve the problem of converting them to numbers ... Thank you for your help
Karen L 10 May 2016, 05:12
my excel report don't have this error checking "Converting Forced Text to Numbers".
Is the any setting problem?
Gaurav 28 Apr 2016, 07:10
Thanks for the provided solutions..
Hoda 27 Feb 2016, 19:26
Thanks for this solution, it saved me hours of pain!
Benjamin Rawlings 30 Jan 2016, 19:46
I have two columns that are supposed to be currency, but none of the entries show as such. The values are in a table, if that matters. It is 2007. Also another column shows the error, "Inconsistent calculated formula" although I don't have formulas and the columns are formatted as text. I cannot seem to clear the errors either. The spreadsheet is a form I did not create, but use to store data.
AH 08 Dec 2015, 16:46
None of your solutions is working. You need to acknowledge that they may not. If you don't know everything, you don't, but don't be dogmatic.
M. Dragicevic 27 Nov 2015, 11:09
Your tip is INVALUABLE and saved me some time, I appreciate it very much. Keep up the good work.
Greetings from Milan, Italy
ANEESH P R 23 Oct 2015, 06:32
Unable to sum the values for the mentioned.
tried many ideas like find and replace,text to columns etc.
paulvlk 30 Jul 2015, 12:29
Copying columns from some text files, e.g. HTML results in an extra character inserted AFTER the number in a text format which resists conversion to a proper number.
Get one of those cells up in the formula bar and place the cursor at the end of what is displayed. You will see there's an extra space after that obstinate number. Delete that extra space and all will be well.
If you want a bit of automation to it here's one way of getting rid of that unwanted space in a new column - in the example below the unwanted space (in my case in D9 cell) is replaced with a zero which then makes the cell a proper number:
phani 23 Jul 2015, 16:15
I had coppied content from a pdf file and pasted it into notepad and saved it as .txt file. there are some number which are -ve in the table. I couldnt find any means to convert these general text to numbers, I tried everything suggested.
MIchael Armstrong 17 May 2015, 05:32
I often have the problem Kobus just mentioned -- I don't know the format of a particular cell. Is there any way to display all the format information of a cell, so you can start figuring out how to use it? This is especially important when importing data, which often looks like a number, but isn't.
Kobus 16 May 2015, 08:27
I do not know in what format these figures are but I need to convert them into Excel format:
Willy Vanhaelen 05 May 2015, 06:29
Simply remove the parenthesis either manually or with Find and Replace
Sanjay 05 May 2015, 01:17
How can i convert this which are as percentage but appears as text
(1.57%) and (-1.53%)
I tried the value and multiply but that didnt worked
WRosocha 15 Feb 2015, 14:42
When I copy transactions from my bank's web site to Excel, the copy paste special with multiply by 1 or add 0 doesn't work. There must be some hidden character that excel doesn't like. Does anyone know why?
But when I run a macro that strips spurious characters from the data items in the range, the text automatically converts to numbers and I don't need to run these text to number conversions.
Such a macro may be a quicker way than the techniques described here. It works quite nicely, especially if there is no real text in the range.
Michael (Micky) Avidan 17 Jan 2015, 08:31
Try the suggested formula in the linked picture:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
AFS 16 Jan 2015, 11:47
Me telecom provider submits its bill and details of calls in excel format. The cost column contains "currency numbers" in format as £ space value e.g. £ 1.50
Excel does not recognise this as text nor as number and using any operand sum (or Sigma) add divide etc on this cell and all the cells returns either "value" error or zero
How to identify the cell format and or convert the format to the number
Keith 15 Jan 2015, 17:27
Peter that is an AWESOME trick.
This was exactly what I was looking for. It allows me to leave number as text (for formatting reasons) and I am still able to complete my math on a range.
(I kept trying to work with the value function on a range and was unsuccessful.)
Thank you for that wonderful bit of knowledge.
Mark Schreiber 01 Jan 2015, 23:38
If there is a reason for the number to be stored as text, you could convert that text to a number with Value( ). For examples:
Value(A1) + A2
Peter Atherton 30 Dec 2014, 16:32
You can also use SUM as an Array formula to total formatted text. e.g.
=SUM(A1:A6+0) or =SUM(A1:A6*1) Both formulas entered Ctrl + Shft + Enter.
awyatt 29 Dec 2014, 08:25
William: It doesn't work because changing the format of the cell doesn't cause Excel to reparse the contents of each cell. It is the reparsing that is necessary to do the actual conversion from text to a numeric value.
Owen: The TEXT function forces numeric values to their text equivalent. It is, indeed, the opposite of what is discussed in this tip.
Rudra: I covered that in the tip, in the second or third paragraph after Figure 2.
Rudra Sharma 29 Dec 2014, 01:37
Instead of typing 1 and copying it, try copying blank cell. Excel uses blank cell as 0.
Owen Phillips 27 Dec 2014, 22:06
I have the reverse problem to that of Wm. Larson. ie converting numbers to "real" text ie with a ' ^ or " prefix added to the number.
I find I have to use a formula to do this.
eg =TEXT(F8064, "0")
William Larson 27 Dec 2014, 07:32
I would think that selecting the cells and setting the format to number should do so. Can anyone explain to me why Microsoft has decided to make this not work? I.e. is this a feature or a bug?