How to Convert Text to Number in Excel (6 Easy Ways)

It is quite a frustrating situation when the numbers are formatted or stored as text in an Excel spreadsheet. Due to this, you’ll be unable to perform various Excel tasks like creating charts from values, mathematical calculations, or grouping them into arrays. But don’t worry, in this write-up, you’ll learn how to convert text to number in Excel using 6 different ways.

So, without any further ado, let’s get started…

How To Convert Text To Number In Excel?

Follow the below step-by-step methods for converting text to numerical values in MS Excel.

Way 1: Use the ‘Convert to Number’ Option to Convert Text to Number Excel

The very first way that you can try to convert text to number using the option ‘Convert to Number’. This option will eventually help you to convert the data that is been entered in the Excel with an apostrophe.

Here is how you can use this option:

• Initially, select the Excel cells that you need to convert to the number format.
• Then, you’ll see one yellow diamond exclamation symbol near the selection.
• Simply, click on a dropdown >> select the option ‘Convert to Number’.

• Now, you will be getting your text converted to the number.

Way 2: Use Paste Special with Multiply

In Microsoft Excel, Paste Special is a fabulous tool that also helps to convert text to numbers in Excel.

So, here we will use the Paste Special feature to convert the text into numbers and to perform the simple calculation.

Here is how you can do so:

• Enter the value 1, in any blank cell.
• After that select, the particular cell where you type 1 > on the Edit menu. Click Copy.
• And with the values select the cells that you need to convert to numbers.
• Next on Edit menu >> click Paste Special.

• Then under Paste Special operation >> select All >> click Multiply >> OK.

Way 3: Use the ‘Text to Columns’ 

This method works best if 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:

• Choose one column of cells that holds the text.
• And on the Data menu > click Text to Columns.

• After that under Original data type > click Delimited > click Next.
• Next under Delimiters > click to choose the Tab check box > click Next.

• And under Column data format > click General.
• Then click Advanced > make any appropriate settings for the Decimal separator > Thousands separator and click OK.
• Click Finish.

And that all your text is converted to numbers.

Way 4: How to Convert Text to Number in Excel with the VALUE Function

The VALUE function is a handy way that can be used for converting the text to numbers.

Here you have to type the below command in the cell:

= VALUE (text)

• Here, text is the value that you need to convert to a numerical value.

In case, if your text contains non-numerical characters, the VALUE function returns a #VALUE! error. And it doesn’t give you the result that you want.

= VALUE (B3)

Now, the above formula can be used to convert text in the Excel cell B3 to a numerical value and then copy & paste a formula in order to convert the complete column.

Way 5: Excel Convert Text To Number Using VBA

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

 It is found that in many cases all the above-mentioned solution fails to work. Well in a such situation, using VBA works great.

The following VBA Macro code is provided on 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 an error on execution, then you need to do the following changes in coding:

 For Each xCell In Selection

 xCell.Value = CDec(xCell.Value)

Next xCell

Way 6: Changing Cell Format

Changing the cell format is another yet way that can assist you to convert text to number Excel successfully.

• For this, you have to right-click on the cell(s).
• Click on “Format Cells.”

• After this, choose “Number” tab >> click on “Number” on a left.

• Now, you can adjust the Decimal Places >> click “OK.”

Related FAQs:

What Is the Shortcut to Convert to Number in Excel?

The Ctrl + Alt + V is the shortcut to convert to numbers. All you need to do is to select the cells that you need to convert to numbers, then press the Ctrl + Alt + V keys simultaneously.

How Do I Convert Text to Number in Excel VLOOKUP?

By using the Value Function, you can convert the text to number in Excel VLOOKUP.

How Do I Convert Text to Number in Excel Cell?

You can convert text to number in Excel cell using the Paste Special feature, error checking, or Value Function.

Also Read: How to Convert Access To Excel File? [The Definitive Guide]

To Sum Up

Well, this is all about how to convert text to number in Excel in easy ways. I tried my level best to put together the possible steps that will help you out.

All you need to do is to follow the given steps one by one and convert text to number in MS Excel effortlessly.

Thanks for reading this post!