In some cases, numbers copied may appear as texts, and this leads to incorrect calculations when you use affected cells as part of a formula or function in excel. Functions such as SUM or AVERAGE would ignore cells with text values while other functions such as VLOOKUP that requires an exact match to the number you are looking up would return a N/A because you are looking up a number whilst the first column in the table array contains texts instead.
In today’s post we would take you through various ways to quickly convert texts to numbers. The methods we would be touching on are:
- Converting Using the in-built Convert to Number Option
- Convert using Paste Special Function
- Convert Using Text to Columns
- Convert Using Value Function
Convert Text to Numbers using in-built convert to Number Option
Excel usually detects numbers stored as texts, and notifies you through a green triangle by the top left corner of the cell. On selecting the cell you would see a yellow diamond shaped icon with a dropdown with options to convert the text to number. Let’s treat this in more details below:
Step 1: Select the cells with the green triangle by the top left corner of the cell by using shift combined with the arrow keys depending on the location of the other cells or you can use your mouse to drag from top to bottom or from left to right.

Step 2: Click on drop down on the yellow shaped icon. You would see a convert to number option, click on it.

Result: The text is converted to numbers and the green triangle by the top left of each cell highlighted in step 1 disappears.

Convert Text to Number using Paste Special Function
Another easy method of converting text to number is by using the paste special function. With this method, all you need to do is to multiply the text with the number 1. Let’s show you the steps below.
Step 1: Type the number one into any cell in your worksheet

Step 2: Copy the cell containing 1 by using Ctrl + C

Step 3: Select the range you want to convert to numbers

Step 4: Right click and select Paste Special

Step 5: Look to the bottom right in the paste special dialog box and select multiply within the operation category.

Result: Numbers are converted to number as evidenced by the green triangle at the top left corner disappearing.

Convert Using Text to Columns
The text to column option is best used for data in a single column. It is quick and easy to use. Let’s see th steps below:
Step 1: Select all the cells you want to convert to numbers

Step 2: Go to the Data tab > Data tools > Text to columns

Step 3: Select Delimited in step 1 of step 3 in the convert text to column wizard and click Next

Step 4: In step 2 of the text to column wizard, select Tab as the delimiter and click on next

Step 5: In the final step of the Text to column Wizard, ensure to select General in the column data format. You can specify the destination as seen below. However, if this field is left blank, excel would replace the original data set.

Convert Using Value Function
The value function can be used with a combination of other functions to convert text to numbers. Most times, Value function alone is sufficient to convert text to numbers, however if there are value errors, you can convert the data with a combination of Value, Trim & Clean functions. Let’s see how they can be combined below, but first, we would apply the Value function to our data set.
To apply the value function, all you need to do is type in the value function and select the cell ass seen below. Then you can double click to cascade to other cells.

If the value function alone doesn’t work, try combining it with Trim & Clean function as seen below. We are assuming that we want to convert cell C3.
What do each of these functions do? Let’s quickly see below:
Value Function: As already stated, the value function converts any text that represents a number into an actual number
Trim Function: Removes leading or trailing spaces
Clean Function: Removes extra spaces and non-printing characters that may appear if the data is downloaded or imported from a database