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
Three methods of forcing a TEXT cell TO a NUMBER format
Sometimes, Excel recognizes a cell with a number value in it as text. You will notice that when you conduct calculations: you will receive 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.
Method 1: Force to number manually
In many cases it’s enough if you enter the cell for editing it and confirm by pressing enter on the keyboard.
- First: Make sure that the cell is formatted as number. Therefore press Ctrl
+ 1 
on the keyboard and set the “Category” on the “Number” tab to “Number. - Now enter the cell by pressing F2
on the keyboard. - Then press enter
.
Is the cell a number cell now?
Method 2: Force to number by multiplying with 1
Luckily, if you are converting a text format to a number format, there is an easy workaround:
- Change the format of the cell to “Number” by pressing Ctrl +1 (Mac: Cmd + 1) and select number in the “Number”-tab. Confirm by pressing Enter on the keyboard or clicking OK.
- Write “1” into an empty cell.
- Copy this cell.
- Select the cells you want to force to the number format.
- “Paste Special” by press Ctrl + Alt
+ v 
(Mac: Ctrl + Cmd + v). - At the now opened paste special window select “Multiply”. If you don’t want other changes as background/font color, you might want to select “Value” as well.
- Press “OK”.
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 as text, just multiply it by one in a formula. E.g.=A1*1
Method 3: Force 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 want 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’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Three Methods of forcing a NUMBER cell TO a TEXT format
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.
Method 1: Just switch the format to text
Format the cell to text by pressing Ctrl + 1 . Please note that sometimes Excel automatically switches back to number.
Method 2: Add an ‘ (apostrophe)
The more reliable 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
Method 3: Force to Text with Professor Excel Tools
If you want to bulk insert the single quotation marks to many cells, you can use our Excel add-in Professor Excel Tools. Just select all your cells, go to the Professor Excel ribbon and click on “Force to Text”.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Forcing any other cell format conversion by using VBA
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:
- Select all the cells you want to change the format.
- Change the format the cell to “Number” by pressing Ctrl +1 at the same time and select your desired cell format under the “Number”-tab.
- Make sure, that your cells are still selected.
- Press Alt + F11
for opening the VBA editor. - Press Ctrl + G
for opening the “Immediate”-Window. - Paste the following code and press enter .
For Each cl In Selection:cl.Formula=cl.Formula:Next clNote: This way works also for converting a text cell to number.
thx
You are welcome! 🙂