Convert Texts to Numbers

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.

Convert Text to Numbers

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

Convert Text to Numbers 2

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 Numbers 3

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

Text to Numbers

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

Text to Numbers 2

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

Text to Numbers 3

Step 4: Right click and select Paste Special

Text to Numbers 4

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

Text to Numbers 5

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

Text to Numbers 6

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

Text to Numbers 7

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

Text to Numbers 8

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

Text to Numbers 9

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

Text to Numbers 11

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 Text to Numbers 4 1

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.

Convert with Value Function

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.

=VALUE(TRIM(CLEAN(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

Leave a Comment