Table of Contents
Google Sheets provides a variety of formulas and functions to convert text into numerical values. In this article, 3 examples are given to demonstrate how to use the VALUE, NUMBERVALUE and REPLACE functions to convert text to numbers. The VALUE function is used to convert text that is in the form of a number, while the NUMBERVALUE function is used to convert text that is in the form of a number but also includes a currency symbol or a percentage sign. The REPLACE function is used to replace one set of characters with another set of characters, which allows you to convert text to numbers.
You can use the following formulas to convert text to numbers in Google Sheets:
Method 1: Convert Text to Number
=VALUE(A1)
Method 2: Convert Currency to Number
=TO_PURE_NUMBER(A1)
Method 3: Extract Numbers from Text
=SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789")))
The following examples show how to use each formula in practice.
Example 1: Convert Text to Number
We can type the following formula into cell B2 to convert the text in cell A2 to a number:
=VALUE(A2)
We can copy and paste this formula down to the remaining cells in column B:
All of the cells in column B are now a number.
To verify this, we can type =ISNUMBER(B2) in cell C2:
We can see that the value in cell B2 is a number since the ISNUMBER() function returned TRUE.
Example 2: Convert Currency to Number
We can type the following formula into cell B2 to convert the currency value in cell A2 to a number:
=TO_PURE_NUMBER(A2)
We can copy and paste this formula down to the remaining cells in column B:
All of the cells in column B are now a number.
To verify this, we can type =ISNUMBER(B2) in cell C2:
We can see that the value in cell B2 is a number since the ISNUMBER() function returned TRUE.
Example 3: Extract Numbers from Text
We can type the following formula into cell B2 to extract numbers from the text in cell A2:
=SPLIT(A2,CONCATENATE(SPLIT(A2,".0123456789")))
We can copy and paste this formula down to the remaining cells in column B:
All of the cells in column B are now a number.
To verify this, we can type =ISNUMBER(B2) in cell C2:
We can see that the value in cell B2 is a number since the ISNUMBER() function returned TRUE.