6 Ways To Converting Text To Number Quickly In Excel

Often times Excel recognize number as text.

That could lead to a lot of problems.

1. Not able to perform mathematic operations
2. Lead to error
3. Cells are aligned to the left

In this article, I am going to show you 6 ways to convert text into number.

Example

Before you go for any solutions, make sure you identify the type of your cells.

There are actually two types. One is number format as text. One is number with a leading apostrophe. (You can play around with the embedded Excel to know it better)

Not every 6 solutions are applicable to both situations.

Sample Workbook

Download the workbook to practice it by yourself!

Expected Outcome

How would the cell look like if we successfully turn text into number?

1. The green flag in the upper left corner of the cell will disappear
2. The value would be aligned to the right

Option 1: Change the format of the cell as number

Step 1: Select the cells stored as text

Step 2: Choose “Number” in number format drop-down list

This only works when there is no leading apostrophe. Sometimes, it just doesn’t work.

So, I don’t really recommend using it alone. you may end up leaving some cells formatted as number and some still as text.

But I am still listing this out because it is the faster options as long as it works.

Option 2: Convert to Number

This is the probably the easiest way.

Yet, it is the slowest way. Sometimes this causes Excel to crash.

So, I would recommend this only if the size of your data is small.

Step 1: Select cells stored as text

Step 2: Press the warning sign

Step 3: Select “Convert to Number”

Option 3: Value( ) Function

Step 1: Input the formula in a random cell

=Value(A2)

Step 2: Drag the formula to remaining cells

End Result (Option 3 Value( ) Function)

The cell stored as text remains. We are just using Value() to turn a text into numeric value.

Option 4: Text to Columns

Step 1: Select the cell

Step 2-3: In the “Data” tab, select “Text to Columns”

Step 4: Press “Finish”

End Result (Option 4 Text to Columns)

Option 5: VBA

Read How to Insert & Run VBA code in Excel – VBA101 if you don’t know how to use the VBA code.

Paste the below VBA script and run it.

Sub Convert_text_to_number()
'The script is created by Dollar Excel.
'Subscribe to Dollar Excel newsletter for more free resources like this.

      For Each Cell In Selection
                      Cell.Value = Cell.Value
      Next 
End Sub

Option 6: Paste Special

The final option is “Paste Special”. “Paste Special” is a super powerful feature of Excel.

Yet, not many people know “Paste Special” can change the format of a cell from text into number.

Step 1: Type “1” into a blank cell

Step 2: Copy the cell (you can use Ctrl C)

Step 3: Select the cell you want to format as number

Step 4-6: In home tab, select “Paste” >”Paste Special”

Step 7-8: In “Paste Special” dialog, select “Multiply” and press “OK”

End Result (Option 6 Paste Special)

Both column A and column B are turned into number.

You can delete the cell contains “1” after the operation.

Discovery (Option 6 Paste Special)

It seems that any mathematical operations can turn a text into a number.

I have tried the following operations and they all worked like a charm.

1. Add “0”
2. Subtract “0”
3. Multiply by “1”
4. Divide by “1”

So I guess the trick here is to do any kind of mathematic operations but to make sure you didn’t change the value.

Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!