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.

Converting Forced Text to Numbers

by Allen Wyatt
(last updated December 27, 2014)

28

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:

  1. Enter the value 1 in an empty cell.
  2. Select the cell and press Ctrl+C. The value is now in the Clipboard.
  3. Select the range of cells you want to convert to numbers.
  4. Display the Home tab of the ribbon.
  5. 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.)
  6. Figure 2. The Paste Special dialog box.

  7. Make sure the Multiply radio button is selected.
  8. 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:

  1. Select the range of cells you want to convert to numbers.
  2. Display the Data tab of the ribbon.
  3. Click the Text to Columns tool in the Data Tools group. Excel displays the Convert Text to Columns Wizard. (See Figure 3.)
  4. Figure 3. The Convert Text to Columns Wizard.

  5. 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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the Mouse to Adjust Your View of 3-D Graphs

Want to adjust the perspective from which your 3-D graph is viewed? Excel allows you to use the mouse to rotate the graph in ...

Discover More

Creating a Center Across Selection Button

The ability to center text across a range of cells has long been a staple of experienced Excel users. Here's how to create a ...

Discover More

Displaying a Message in the Status Bar

A great place for your macro to display status information is, well, in the status bar. Displaying the information is easy, ...

Discover More

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!

More ExcelTips (ribbon)

Copying Between Instances of Excel

Copying information between two instances of Excel is different than copying information between two worksheets opened in a ...

Discover More

Creating Selections

Want a really easy way to create a selection of a group of cells? Discover how to use the Extend key to make this task easier ...

Discover More

Finding Unused Names

After months or years of naming things (such as cell ranges), you may find your workbook cluttered with a bunch of names that ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2016-12-09 15:27:00

Marvella Jackson

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


2016-10-23 09:50:47

Kevin Kweenarto

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


2016-07-16 13:12:05

suraj kumar

$ Sixty Six Million Three Hundred Sixty Four Thousand Three Hundred Thirty Four Dollars and Twenty Two Cents AND Thirty %


2016-06-08 23:50:46

Dinesh Singh Chauhan

It is excellent site to get useful tips.


2016-06-08 07:07:50

Neren

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


2016-05-10 05:12:55

Karen L

my excel report don't have this error checking "Converting Forced Text to Numbers".

Is the any setting problem?


2016-04-28 07:10:54

Gaurav

Thanks for the provided solutions..


2016-02-27 19:26:25

Hoda

Thanks for this solution, it saved me hours of pain!


2016-01-30 19:46:36

Benjamin Rawlings

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.


2015-12-08 16:46:31

AH

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.


2015-11-27 11:09:08

M. Dragicevic

Your tip is INVALUABLE and saved me some time, I appreciate it very much. Keep up the good work.
Greetings from Milan, Italy


2015-10-23 06:32:06

ANEESH P R

Unable to sum the values for the mentioned.
please resolve.

 5,000.00 
 1,950.00 
 5,000.00 
 3,000.00 
 1,400.00 
 5,000.00 

tried many ideas like find and replace,text to columns etc.


2015-07-30 12:29:04

paulvlk

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:

=REPLACE(D9,LEN(D9),1,0)


2015-07-23 16:15:12

phani

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.


2015-05-17 05:32:54

MIchael Armstrong

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.


2015-05-16 08:27:35

Kobus

I do not know in what format these figures are but I need to convert them into Excel format:

8,160.00
65.00 Cr



2015-05-05 06:29:02

Willy Vanhaelen

@Sanjay
Simply remove the parenthesis either manually or with Find and Replace


2015-05-05 01:17:29

Sanjay

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


2015-02-15 14:42:54

WRosocha

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.


2015-01-17 08:31:46

Michael (Micky) Avidan

@AFS,
Try the suggested formula in the linked picture:
http://postimg.org/image/hhffd8lhd
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-01-16 11:47:03

AFS

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


2015-01-15 17:27:35

Keith

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.


2015-01-01 23:38:06

Mark Schreiber

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


2014-12-30 16:32:22

Peter Atherton

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.


2014-12-29 08:25:37

awyatt

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.

-Allen


2014-12-29 01:37:20

Rudra Sharma

Instead of typing 1 and copying it, try copying blank cell. Excel uses blank cell as 0.

With Regards
Rudra


2014-12-27 22:06:26

Owen Phillips

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")


2014-12-27 07:32:41

William Larson

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?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.