Force Cell to Text/Number Format in Excel: Easy Tricks!

force, cell, format, formatting, text, number

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.

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

force, number, excel, manually, enter, cell
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.

  1. First: Make sure that the cell is formatted as number. Therefore press Ctrl computer_key_Ctrl+ 1 computer_key_num_row_1on the keyboard and set the “Category” on the “Number” tab to “Number.
  2. Now enter the cell by pressing F2 computer_key_F2on the keyboard.
  3. Then press enter computer_key_Enter.

Is the cell a number cell now?

Method 2: Force to number by multiplying with 1

multiply, 1, one, cell, force, number, excel
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:

  1. Change the format of the cell to “Number” by pressing Ctrl computer_key_Ctrl+1 computer_key_num_row_1(Mac: Cmd + 1) and select number in the “Number”-tab. Confirm by pressing Enter on the keyboard computer_key_Enteror clicking OK.
  2. Write “1” into an empty cell.
  3. Copy this cell.
  4. Select the cells you want to force to the number format.
  5. “Paste Special” by press Ctrl computer_key_Ctrl+ Alt computer_key_Alt+ v computer_key_V(Mac: Ctrl + Cmd + v).
  6. 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.
  7. 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’

force, convert, number, excel
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 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.

Professor Excel Tools Box

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, text, cell
Format cell as text as the first step.

Format the cell to text by pressing Ctrl computer_key_Ctrl+ 1 computer_key_num_row_1. Please note that sometimes Excel automatically switches back to number.

Method 2: Add an ‘ (apostrophe)

format, cell, text, apostrophe, '
Insert an ‘ for forcing a cell to the text format.

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

Bulk force to text with Professor Excel Tools.
Bulk force to text with Professor Excel Tools.

Professor Excel Tools Box

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

force, format, vba, refresh, macro
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:

  1. Select all the cells you want to change the format.
  2. Change the format the cell to “Number” by pressing Ctrl computer_key_Ctrl+1 computer_key_num_row_1 at the same time and select your desired cell format under the “Number”-tab.
  3. Make sure, that your cells are still selected.
  4. Press Alt computer_key_Alt+ F11 computer_key_F11for opening the VBA editor.
  5. Press Ctrl computer_key_Ctrl+ G computer_key_Gfor opening the “Immediate”-Window.
  6. Paste the following code and press enter computer_key_Enter.
For Each cl In Selection:cl.Formula=cl.Formula:Next cl

Note: This way works also for converting a text cell to number.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.


Leave a comment

Your email address will not be published. Required fields are marked *