Often times Excel recognize number as text.

That could lead to a lot of problems.

- Not able to perform mathematic operations
- Lead to error
- 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?**

- The green flag in the upper left corner of the cell will disappear
- 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**)

**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**)

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

- Add “0”
- Subtract “0”
- Multiply by “1”
- 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!