Often you may want to assign a number value to specific text values in Excel.
For example, suppose you have the following dataset that contains information about sales made by employees at different stores for some company and you’d like to assign number values based on specific stores:
The easiest way to do this is by using the SWITCH function in Excel, which 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 Excel
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)
This formula uses the SWITCH function, which uses the following basic syntax:
SWITCH(value to switch, find1, return1, find2, return2, ...)
In our particular formula, we analyze 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: You can find the complete documentation for the SWITCH function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Find First Occurrence of a Value in Column
Excel: Find First Value Greater than Specific Number
Excel: How to Find First Non-Zero Value in Row
