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 January 28, 2019)

37

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

Progression Indicator in a Macro

When your macro is humming along, minding its own business, a user watching the screen may not see any activity and ...

Discover More

Returning Zero When a Referenced Cell is Blank

Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...

Discover More

End-of-Month Calculations

Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Deleting Rows before a Cutoff Date

If you are using Excel to work with data that is date-centered, you may want to delete some of the data before a specific ...

Discover More

Displaying an Input Format in a Cell

Want to show a user, in a cell, what you expect their input to look like? Unfortuantely it cannot be done natively in ...

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 5 + 7?

2019-07-18 00:36:58

Madi

Thanks!! This definitely helped!!


2019-05-03 00:59:51

Cassie

Just what I needed to explain the issue with some missing values from a cumulative of a row - Thanks


2019-04-25 15:57:56

Diana

I am creating a macro to clean up data in a spreadsheet. One of the columns I convert to numbers, but when I run my macro all the steps are executed accept the convert to number column. How can I incorporate this step in my macro?


2019-03-29 23:05:51

Kevin

It works for me. I have lots of number data that got moved as text. This help convert them back.


2019-03-27 10:28:05

Al

This tip did not work for me.


2019-02-27 05:44:39

Jason

Thanks Allen, very useful ! Saved one of my users a lot of manual work.


2019-02-05 11:24:33

Peter Atherton

Ramesh

This sounds like homework. Computers display 15 characters for integers and up to 20 for decimals. Anything else has to be shown as text. The calculation has to be done as in primary school and numbers are carried over into the next column.

Say you enter 20 single digits into columns and copy them down. Add them with the following

Cell U3:= =(SUM(V1:V3)-V4)/10
Cell U4:= =MOD(SUM(U1:U3),10)

Now you have to concatentate all the cells to get your (text) answer. I used this UDF
=JOINCELLS(C4:V4,"")

The code for this is:
Function JOINCELLS(ByVal ref As Range, _
Optional separator As String = ", ", _
Optional include_blanks As Boolean = False, _
Optional Order As Integer = 0) As String

Dim c As Range, str As String, X As Integer

X = Len(separator)
Select Case Order
Case Is = 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = str & c & separator
ElseIf include_blanks Then
str = str & c & separator
End If
Next c
GoTo ReturnJoin
Case Is > 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = c & separator & str
ElseIf include_blanks Then
str = str & c & separator
End If
Next
End Select
ReturnJoin:
JOINCELLS = Left(str, Len(str) - X)
End Function

(see Figure 1 below)

Figure 1. 


2019-02-04 06:15:14

Ramesh

I want the result in 20 numbers in Excel.(A2 =20 numbers and A3 =20 numbers A1+A2=A3)
I have taken A1=Text A2=Text Data type
Maximum numbers Excel is taking upto 15 characters.
when I add 2 numbers I am getting upto 15 numbers the other 5 numbers are displaying as '00000'

Thanks In Advance
-Ramesh


2018-01-05 15:26:58

Jo

"This works because Excel multiples each cell in the range (step 3)"
It should read "Excel multiplies".


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.