Google Sheets: How to Assign Number Value to Text


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:

Google Sheets assign value to text

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) 

This formula uses the SWITCH function, which uses the following basic syntax:

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 here.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: Set Minimum and Maximum Values in Formulas
Google Sheets: Use VLOOKUP to Return Minimum Value
Google Sheets: How to Find Most Frequent Value

Leave a Reply

Your email address will not be published. Required fields are marked *