7 Easy Ways To Convert Text To Numbers In Excel

Excel is undoubtedly a powerful application of Microsoft Office suite. In this powerful application there are plenty of Excel functions allotted to perform complex tasks easily and effortlessly.

But in some cases, Excel becomes a big disaster.

Today in this article I am going to describe a common problem of how to convert text to number in Excel.

When the user import data from some source, like Access or text files then in some cases the numbers might get formatted as text in cells

Well, this is a quite frustrating situation as, when the numbers are formatted or stored as text the entire calculations and sorting goes wrong.

But don’t take tension as there are several methods that will help you to convert text to number in Microsoft Excel.

This issue causes the functions such as SUM and AVERAGE to ignore values in these cells. These text strings might hold an actual text in addition to the numbers you need to covert

However, if you want to become more productive in Excel and want to learn powerful Excel function and features, then Learn Advance Excel Easily.

How To Convert Text To Number In Excel

To convert Excel text to number, try any of the below-given methods and check which help you out.

Please Note: Every given method assume you have already changed any cell number formatting in cells to General. Follow the steps to do so:

  • On Format menu in Excel > click
  • Then on Number tab > under Category > click General and > OK.

Now follow the given methods one by one:

Step 1: Retype Values in the Cells

On the Format menu,> click Cells and then > click Number tab for changing the number format of the cells. After that retype the numbers

Hope doing this will help you to convert Excel text to number.

Step 2: Select “Edit Directly in Cell”

To do this, follow the given steps and check if this helps you to convert text to number in Excel.

  1. First in the Tools menu > click Options (in Windows) > or In Mac click Preferences on the Edit menu.
  2. Then on the Edit tab,> check that the Edit directly in cell check box is selected > and click OK.
  3. And double click the cell you want to format > press Enter.

Step 3: Use Paste Special with Multiply

Doing this will help your Excel convert text to numbers, so here follow the steps to do so:

  1. Enter the value 1, in any blank cell.
  2. After that select, the particular cell where you type 1 > on the Edit menu click Copy.
  3. And with the values select the cells that you need to convert to numbers.
  4. Next on Edit menu > click Paste Special.
  5. Then under Operation > click Multiply, and Under Paste > click Values > click OK.

If you are getting any issue regrading pasting your data in Excel worksheet then check out our post: 5 Working Solutions to Fix Microsoft Excel Cannot Paste Data Error

Step 4: Eliminate Hidden Characters and Spaces

This step works well if the data is arranged in a single column or row. This tip utilizes the TRIM function and the CLEAN function for removing extra spaces and nonprinting characters that might be imported with the file.

Following example assumes the data is in column A and starts in row 1 ($A$1).

Follow the given steps to use this example:

  1. To the right of column, A insert a column by selecting column B > click Columns on Insert menu
  2. The in the first cell of the inserted column (B1), type the following
  3. After that, in column B > to the right of the cell choose all cells that contain data in column A
  4. Next, on the Edit menu,> point to Fill > then click Down.
    The new column contains values of text in column A
  5. Then with same range selected > on the Edit menu > click Copy
  6. Select cell A1 > click Paste Special on Edit menu. Then under Paste > click Values > click OK to paste the converted values back on top of column A.
  7. And by selecting the column to delete the column B > click Delete on Edit menu

You can see the text that was in column A is in number format now.

Step 5: Utilize Visual Basic for Applications Procedure

Microsoft offers programming examples for illustration only, and without warranty either expressed or implied.

It includes but is not limited to, the implied warranties of merchantability or fitness for a particular reason.

In this article, we suppose that you are familiar with a programming language that is being confirmed and with the tools that are utilized for creating and to debug procedures.

Microsoft support engineers can help in describing the functionality of particular process but they would not change these examples to provide added functionality or build procedures to meet your particular requirements.

Create a Visual Basic for Applications macro to re-enter the numbers in the chosen cell or range of cells.

Follow the steps to do so:

  1. Choose cells that exhibit the behavior.
  2. If you haven’t done so, then on the Format menu click Cells  > click the Number tab to modify number format of the cells
  3. Next, on Tools menu,> point to Macro > click Visual Basic Editor
  4. And on the Insert menu > click Module.
  5. After that type following macro code in the new module:
  6. Next, on File menu,> click Close and Return to Microsoft Excel
  7. Reselect affected cells if not already selected.
  8. Next on Tools menu > point to Macro > click Macros. In the Macro name list > click Enter_Values > click Run.

Step 6: Utilize Text to Columns

This method works best of data is arranged in a single column. The example presumes that the data is in column A and starts in row 1 ($A$1). Follow the steps to do so:

  1. Choose one column of cells that hold the text.
  2. And on the Data menu > click Text to Columns.
  3. After that under Original data type > click Delimited > click Next.
  4. Next under Delimiters > click to choose the Tab check box > click Next.
  5. And under Column data format > click General.
  6. Then click Advanced > make any appropriate settings for the Decimal separator > Thousands separator and click OK.
  7. Click Finish.

And that all your text is converted to numbers.

Step 7: Excel Convert Text To Number Using VBA

Another very interesting way to make your Excel Convert Text to Number is by using the VBA code. It is well applicable for Excel 2003/2007 application version.

 It is found that in many cases all the above-mentioned solution fails to work. Well in such situation Excel Convert Text to Number using VBA works superb.

This following VBA Macro code is provided in the Microsoft page to make Excel convert Text to Number.

 Sub Enter_Values()

 For Each xCell In Selection

 xCell.Value = xCell.Value

Next xCell

End Sub

If in case the above VBA code shows error on execution, then you need to do following changes in coding:

 For Each xCell In Selection

 xCell.Value = CDec(xCell.Value)

Next xCell

Try it, you will surely going to find this step too easy for converting text to number in Excel.


Well, this is all about how to convert text to numbers in Excel.

I tried my best to put together the possible steps that will help you out.

Follow the given steps one by one and easily make your Excel convert text to number.

Good Luck!!!

7 Easy Ways To Convert Text To Numbers In Excel
Article Name
7 Easy Ways To Convert Text To Numbers In Excel
Try the easiest ways to make your Excel convert Text to Numbers. Step 1: Retype Values in the Cells ; Step 2: Select "Edit Directly in Cell" and so on…
Publisher Name
Repair MS Excel Blog
Publisher Logo

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.