

You are changing the cell format, for example from “text” to “number” and nothing happens? Or the other way around, you want to enter a phone number but Excel automatically converts it to a number? Unfortunately, Excel is very unpredictable when it comes to text/number formatting. Here is what you should do in order to force Excel to accept your desired format.
Contents
Sometimes, Excel recognizes a cell with a number value in it as text. You will notice that when you conduct calculations: you will get a #VALUE! error. This problem can be quite annoying because some cells can be very persistent. In this article we will explore ways of how to force a text cell to the number format.
There are basically three approaches for forcing a cell to the number format. Doing it manually, multiplying by 1 or if that doesn’t work using additional tools.
First try: Go into cell and press enter.
In many cases it’s enough if you enter the cell for editing it and confirm by pressing enter on the keyboard.
Is the cell a number cell now?
Force Excel to change the format from Text to Number by multiplying all cells with “1”.
Luckily, if you are converting a text format to a number format, there is an easy workaround:
Now Excel is forced to do a multiplication and therefore has to change the cell format to number. If it doesn’t work, you might want check, if there are any “not number format-able” characters in your cells, a space or a ‘ for example.
One more tip: This also works within formulas. If your cell is recognized as text, just multiply it by one in a formula. E.g. =A1*1
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Comfortably convert text cell to number with Professor Excel Tools.
You want a more convenient way? The Excel add-in ‘Professor Excel Tools‘ provides a feature called ‘Force to Number’: It is a powerful tool for converting a text cell to number by executing several steps.
Just select your cell which you wan’t to convert to a number value and click on the ‘Force to Number’ button in the center of the Quick Cell Functions group. Please feel free to download the free trial version on the button below.
This function is included in our Excel Add-In 'Professor Excel Tools' Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Sometimes, you don’t want Excel to automatically convert a text to a number, for example, when you type a phone number which starts with “0”. If Excel converts it to a number, the 0 in the beginning will be deleted.
We’ve already seen the worst case if Excel misunderstands a formatting. In our example, the MI5 bugged the wrong phones because the phone number in their Excel spreadsheet had some formatting issues.
There are two different ways to avoid Excel converting characters to a number.
Format cell as text as the first step.
Format the cell to text by pressing Ctrl
Insert an ‘ for forcing a cell to the text format.
The safer way is to force a number cell to text is to put an ‘ (apostrophe) at the beginning of your text (or in our case the phone number). Let’s say, you want to type the phone number 004912345678. In order to preserve the 00 in the beginning, type it like this: ‘004912345678
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Force any format to an Excel cell by using a short VBA code.
For all other conversions, e.g. a zip-code, the fastest way is probably a short VBA code. This way might be a little bit tricky, but if you stay with the following recipe, it shouldn’t be a problem:
For Each cl In Selection:cl.Formula=cl.Formula:Next cl
Note: This way works also for converting a text cell to number.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 1
thanks
thx