Table of Contents
To assign a number value to text in Google Sheets, you can use the =VALUE() function. This function converts text to a number by removing any non-numeric characters and decimal points. Simply enter the text you want to convert as the argument inside the parenthesis of the function. This will return the numerical value of the text, which you can then use in calculations and formulas. This is useful for converting data that contains both text and numbers into a format that can be used for numerical analysis.
Often you may want to assign a number value to specific text values in Google Sheets.
For example, suppose you have the following dataset that contains information about sales made by various employees and you’d like to assign number values based on the stores that each employee works at:
We can accomplish this by using the SWITCH function in Google Sheets.
This function allows you to return specific values that correspond to matching values from a list.
The following example shows how to use this function in practice.
Example: How to Assign Number Values to Text in Google Sheets
Suppose you would like to assign the following number values to specific stores:
- North: 1001
- East: 1002
- South: 1003
- West: 1004
You can type the following formula into cell D2 to do so:
=SWITCH(B2, "North", 1001, "East", 1002, "South", 1003, "West", 1004)
You can then click and drag this formula down to each remaining cell in column D:
The new Store ID column now contains a number value that corresponds to the specific text value in the Store column.
How This Formula Works
Recall the formula that we used to assign number values to text values:
=SWITCH(B2, "North", 1001, "East", 1002, "South", 1003, "West", 1004)
SWITCH(cell, case1, value1, case2, value2, ...)
In our example, we look at cell B2 and do the following:
- Look for “North” – if found then return 1001
- If not found, then look for “East” – if found then return 1002
- If not found, then look for “South” – if found then return 1003
- If not found, then look for “West” – if found then return 1004
By using this logic, we’re able to assign number values to each specific text value.
Note #1: In this example we used the SWITCH function with four unique values but you can use the SWITCH function with any number of values that you’d like.
Note #2: You can find the complete documentation for the SWITCH function in Google Sheets .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: