How To Convert Text To Numbers In Excel Using 4 Easy Methods

How to Convert Text to Numbers in Excel? (Step by Step)

There are many ways we can convert text to numbers in Excel. We will see them one by one.

Using quick convert text to numbers Excel option.Use a special cell formatting method.Using the Text to ColumnText To ColumnText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more Method.Using the VALUE function.

#1 Using quick Convert Text to Numbers Excel Option

It is probably the simplest of ways in Excel. Many people use the apostrophe ( ‘ ) before entering the numbers in Excel.

#2 Using Paste Special Cell Formatting Method

Now let us move to another way of changing the text to numbers. Here we are using the Paste Special methodPaste Special MethodPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more. Again, consider the same data we used in the previous example.

We must first select the data. Then, we need to click on the error handle box and select the “Convert to Number” option. That would instantly convert the text-formatted numbers to number format, and now the SUM function works well and shows the accurate result.
Step 1: First, we must type the number either zero or 1 in any one cell.
Step 2: Now, we need to copy that number. (We have entered the number 1 in cell C2).
Step 3: Now, we must select the numbers list.
Step 4: Now, we must press ALT + E + S (Excel shortcut keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more for the pastespecial method). That will open up the below dialog box. Select the multiply option. (We can try to divide also).
Step 5: As a result, it would instantly convert the text to numbers, and the SUM formula is working well now.

#3 Using the Text to Column Method

It is the third method of converting text to numbers. It is a bit lengthier process than the earlier two, but it is always good to have as many alternatives as possible.

Step 1: We must first select the data
Step 2: We need to click on the “Data” tab and the “Text to Columns” option.
Step 3: As a result, it will open up the below dialog box and ensure “Delimited” is selected. Click on the “Next button.”
Step 4: We must ensure the “Tab” box is checked and click on the “Next” button.
Step 5: In the next window, we must select the “General” option, select the destination cell, and click on the “Finish” button.
Step 6: Consequently, this would convert text to numbers, and SUM may work now.

#4 Using the VALUE Function

In addition, a formula can convert text to numbers in Excel. The VALUE function can perform the job for us. We must follow the below steps to learn how to do it.

Step 1: First, we must apply the VALUE formula in cell B1.
Step 2: We need to drag and drop the formula into the remaining cells.
Step 3: Then, apply the SUM formula in cell B6 to check whether it has converted or not.

Things to Remember

If we find the green triangle button in the cell, there must be something wrong with the data.The VALUE function can be useful in converting the text string that represents a number to a number.If spacing problems exist, we nest the VALUE function with the TRIM function. For example, =Trim(Value(A1))The “Text to Column” can also correct dates, numbers, and time formats.

How to Convert Text to Numbers in Excel? (Step by Step)#

#1 Using quick Convert Text to Numbers Excel Option#

#2 Using Paste Special Cell Formatting Method#

#3 Using the Text to Column Method#

#4 Using the VALUE Function#

Things to Remember#

Recommended Articles#