Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated April 18, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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, 2013, 2016, 2019, and Excel in Microsoft 365. 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. ...


Copying Headers and Footers

Need to get headers and footers from one document to another? You can use the steps in this tip to help make quick work ...

Discover More

Finding the Size of a Workbook

Keeping tabs on the size of a workbook can be important when using Excel. You have a couple of options that will allow ...

Discover More

Selecting to the Bottom of a Column in a Macro

Need to select a range of cells in a column? This tip can help, as it shows how to select from a specific cell all the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Sequentially Inputting Information

When entering data in a worksheet, you may only want to add information to the cells in a particular range. You can ...

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

Discover More

Undoing an Edit

We all make mistakes. Fortunately, Excel makes it rather easy to undo your makes, right after you make them.

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 one more than 7?

2022-04-21 19:26:24


I'm using "Version 2203 (Build 15028-20204 Click-to-Run)"... perhaps there is a difference between our versions.

If not, or if any difference doesn't matter, perhaps there is a setting that affects this behavior. It's hard to imagine one from my knowledge of the settings, but definitely not impossible.

There is also the kind of difference that is due to one's usage of features and they can be subtle. For example, copy something to the Clipboard that includes spaces and then paste it into an Excel sheet. Likely it will paste as lines of text more or less matching its form in the original material. Then use the Text-to-Columns feature (on something else, say) and specify, oh, "Space" as a second delimiter along with the default "Tab" (either finishing the T2C or escaping after selecting the "Space" delimiter choice). Copy the same thing as before and paste it now. Almost always (almost) you get a very different paste result in that while you will see the lines maintained, you will see each line's text split into cells at the points where spaces exist in the original.

That's a VERY noticeable weird change/difference, but for all I know, there are dozens of similar things. I doubt there's one for this situation, but... like before, who knows? At least until something like this gets someone to delve into it.

However, since the "0" is not needed, why bother with it and have any possible issue?

2022-04-21 04:17:23

Rick Rothstein


You said "Presumably, the leading "0+" (not the one related to the MID() function) is meant to coerce Excel into NOT doing the above. But it fails to do so. The format still changes to text and recalculation still ruins everything" but, unless I am misunderstanding you, does not happen in my copy of XL365... my formula leaves the cell it is in formatted as General and the number in the cell is a real Excel number (right aligned).

2022-04-20 19:05:26


@Rick Rothenstein:

Using "0+" in the testing of each element of the array of values the MID() produces is great. Everything I've ever read says that since it is an arithmetic operation, it should be faster than a function (the VALUE() function I used for the purpose), even blindingly faster. May not be needed in a given use, but it never hurts either, eh?

Personal preference? I take TEXTJOIN() over CONCAT() any time just because it's a wee bit more flexible and if you use something other than "nothing" for the delimiter, it does not add one of them to the end which then usually needs removed. Its form for all that is simple and intuitive (though I'd've gone "Range-Delimiter-Empties" myself as being MORE intuitive were I its author) where anything with CONCAT() usually has an oddity requiring careful thought.

However, it doesn't come down to personal preference.

Using TEXTJOIN(), the output is, of course, text. However, the cell retains its starting format which certainly should not be text. So if you see the cell recalculated for any reason, its output remains the same and all downstream uses of it remain as they were, unaffected by the recalc.

Using CONCAT() though... The format for the cell is changed, literally changed, to text. Recalc that cell and the formula becomes text, not a formula since that's what happens to formulas entered in a text-formatted cell. No bueno.

Presumably, the leading "0+" (not the one related to the MID() function) is meant to coerce Excel into NOT doing the above. But it fails to do so. The format still changes to text and recalculation still ruins everything. I tried it with just the "+" as that is often sufficient, and it does leave it as text (ISTEXT() gives TRUE) but does not change the cell number format TO text.

Using VALUE() to wrap the whole thing instead does work in that it keeps the cell format from changing to text. But the result is a value and while that would usually be MY need, it may not be everyone's.

By the way, in my earlier diatribe about people shunning helper columns and VBA, I mentioned a bit of it being like considering "Boy, I need a third hand" an insult to God. Credit (some credit anyway, er, barely some...) where credit is due: can't remember the author or story title, but it came from a Hugo award winner in the 1960's in which a post-nuclear apocalyptic world apparently had radiation-driven mutancy and those without mutation had gone over the edge into hardcore religion (Christianity of course, not, say, Norse mythology) and considered mutants to be abominations, no matter how nice a pack of folk they were, to the degree that even a careless comment like the above would result in savage punishment as if the person were wishing to BE an actual abomination and therefore an insult to God... Etc. Hijinks ensue, some psy-capable young folk must flee, more hijinks ensue... and 50 years later it still sticks enough to be a working metaphor for me. Just can't remember the author or story name... sigh...

2022-04-18 11:26:29

Rick Rothstein


This shorter formula also works...


2022-04-18 08:53:58

Rick Rothstein


This shorter formula also works...


2020-11-01 03:49:32


The following formula will strip out JUST numerical characters and turn them into a single value. It DOES ignore numerical assistants like "-", ",", and "." as well as fellow travellers such as "(", ")", "_", and any currency markers. But there is a slightly different approach formula that keeps anything of that sort using a list you create or populate:


and one can use LET(), as in: "LET( Value, A1, etc.)", to allow that A1 which occurs twice to exist in only the one place at the start of the formula allowing easy editing of the cell referred to. (One place, easy to locate, no hunting through 23 lines of formula looking for all instances of A1 or using Find & Replace only to find out the string A1 occurs in other ways in the formula.)

The cell reference is in the MID() so using a range gets one single reallllly long joined value (so if summing 6 numbers, each 3-4-5 digits long, one would ahve a resulting single number of 18 to 30 digits rather than six separate numbers). So it would have to be copied down a column or used multiple times in a formula rather than as a simple SUM() formula. Though one might get creative with string building techniques and something like OFFSET() to ease that pain.

However, if not willing or able to hunt down the offending extra characters in one's "unclean" text import, this will clean it with just the existence of a helper column.

(For those who hate helper columns, heck with you. Do the cleaning work or sit, spin, and whine instead. Sick of people who look down on some tools. I get "hard to understand, learn, or use" like a whole macro language but "ew-w-w-w, I hate helper columns"... no. (Not that slowly learning a macro language useful bit by useful bit isn't a good way forward on that front, but some won't even learn simple uses for the Immediate Window 'cuase macro is macro is macro and they ain't gonna do even useful bits of learning where those are concerned. They'd as soon "insult God" by saying "boy, I wish I had a third hand right about now" as to learn any piece of VBA.))

2020-08-27 09:17:01


Thanks, this worked!

2020-06-25 15:19:20


Thank you! I've been looking for this answer for 30 minutes - you solved my problem!

2020-03-05 18:53:18


Best solution on the internet.

2019-07-18 00:36:58


Thanks!! This definitely helped!!

2019-05-03 00:59:51


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


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


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


This tip did not work for me.

2019-02-27 05:44:39


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

2019-02-05 11:24:33

Peter Atherton


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

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
End Select
JOINCELLS = Left(str, Len(str) - X)
End Function

(see Figure 1 below)

Figure 1. 

2019-02-04 06:15:14


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

2018-01-05 15:26:58


"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


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


Thanks for the provided solutions..

2016-02-27 19:26:25


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


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


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


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

2015-07-30 12:29:04


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:


2015-07-23 16:15:12


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


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

65.00 Cr

2015-05-05 06:29:02

Willy Vanhaelen

Simply remove the parenthesis either manually or with Find and Replace

2015-05-05 01:17:29


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


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

Try the suggested formula in the linked picture:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)

2015-01-16 11:47:03


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


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


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.


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

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

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.