Force Any Cell to Your Desired Format in Excel
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
3 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 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.
Method 1: Force to number manually
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.
- 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
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:
- 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 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?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Method 3: Force to number with ‘Professor Excel Tools’
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.
2 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 cell as text as the first step.
Format the cell to text by pressing Ctrl + 1 . Please note that sometimes Excel automatically switches back to number.
Method 2: Add an ‘ (apostrophe)
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 how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
Forcing any other cell format conversion by using VBA
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:
- 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 .
Note: This way works also for converting a text cell to number.
