Assign a Number Value to TextAssign number values to text in Excel using these functions

Written By:
Patrick Louie

In Excel, you may work with different data values and types.

You may work with an  Excel sheet or workbook that entirely comprises numerical values.

You may also work with one that only contains text values. But most of the time, you’ll be working with a mix of both.

Now, what if you want to assign a numerical to a particular text string?

Or why not go even further and assign a respective number for each distinct text string?

Can you do that in Excel?

For example, can we assign 1 to North, 2 to East, 3 to South, and 4 to West?

Well, you can always do it manually, but I assume that that’s not why you’re here.

You’re here to know if Excel has features or functions that can help with this particular task.

And to that, I say there is. In fact, there’s more than one Excel feature or function that can perform the task of assigning numerical values to text strings.

In this article, I’ll be showing you what these functions/features are and how you can use them.

By the end of the article, you should be able to assign a number value to any text whenever you have to.

Let’s get started.

Use SWITCH to Assign a Number Value to Text

For the first method, we’ll be using the SWITCH function, which is an array function.

Currently, it’s only available in the 2019, 2021, and Office 365 versions of Excel. It can be made available in Excel 2016 if it has an Office 365 subscription.

What the SWITCH does is evaluate a value against an array (or list of values).

It then returns the value you assigned to such a value.

If there is no match, the function will then return a default value, which you can also assign (otherwise, it will return a #N/A error value).

The formula for using the SWITCH function is as follows:

=SWITCH(expression,value1,result1,value2,result2…default_value)

Where:

expression – the array or range of cells from which the value will be matched against

value# – refers to the value that will be matched against the array. There can be more than one value

result# – refers to the value that will appear on the cell if there’s a match. You can assign one result per value

default­_value – refers to the value that appears if there’s no match. This parameter is optional )it will result in a #N/A error value if unassigned)

How to Assign a Number Value to Text

Let’s say that we have this dataset:

It contains a list of employee IDs and the region to which they’re assigned. Suppose that we want to assign numbers to the different regions.

We’ll be assigning  1 to North, 2 to East, 3 to West, and 4 to South.

To do so, we’ll be using the SWITCH function.

  • Select an empty cell, preferably one that is adjacent to the array or range of cells to which we’ll be assigning numbers. Make sure that there are enough empty cells below it since we’re using an array function. For our illustration, we’ll be selecting cell C2.

  • In the selected cell, enter the formula for using the SWITCH function. In our illustration, the formula will be =SWITCH(B2:B31,”North”,1,”East”,2,”West”,3,”South”,4). With this formula, if the value is North, the function will return a 1 value, 2 if the value is East, 3 if the value is West, and 4 if the value is South.

  • Press the enter key. The function should return an array that follows the shape of the referenced array (or range). It will contain the results that match the values that we assigned.

  • We have successfully assigned numbers to text in Excel.

Use IFS to Assign a Number Value to Text

For the next method, we’ll be using the IFS function. Currently, it’s only available in the 2019, 2021, and Office 365 versions of Excel.

The IFS function is practically an improved version of the IF function.

With it, you can make several arguments without having to nest several IF statements.

The function will check if any of the stated conditions are met. It will then return the result value that matches the condition.

The formula for using the IFS function is as follows:

=IFS(argument1,result1,argument2,result2…)

Where

argument# – refers to the condition that you want to set. For example, you can set the argument to “A2=North”. You can set multiple arguments with a single IFS statement

result# – refers to the value that appears on the cell if any of the IFS conditions is met. You can set one result per argument. If there’s only one argument, you can set a result for when it is true and another when it is false (optional)

How to Assign a Number Value to Text

Let’s say that we have the same dataset:

This time, we’ll be using the IFS function to assign numbers to the different regions.

  • Select an empty cell, preferably one that is adjacent to the array or range of cells to which we’ll be assigning numbers. For our illustration, we’ll be selecting cell C2.

  • In the selected cell, enter the formula for using the IFS function. In our illustration, the formula will be =IFS(B2=”North”,1,B2=”East”,2,B2=”West”,3,B2=”South”,4). With this formula, if the value in cell B2 is North, the function will return a 1 value, 2 if the value is East, 3 if the value is West, and 4 if the value is South.

  • Press the Enter key to run the IFS function.

  • Copy and paste the formula to the rest of the column (until the next empty row).

  • We have successfully assigned numbers to text in Excel.

Use IF to Assign a Number Value to Text

In the previous methods, we used functions that are only available in the newer versions of Excel (2019 and up). For the next method, we’ll be using a function that you can use for the 2016 and older versions of Excel. It’s the IF function.

With the IF function, you can set an argument (or condition). Then, you can set the values that appear if it’s true or false. You can nest multiple IF statements to make multiple arguments.

The formula for using the IF function is as follows:

=IF(argument1,result1,IF(argument2,result2…))

Where

argument# – refers to the condition that you want to set. For example, you can set the argument to “A2=North”. You can set one IF argument for every nested IF statement

result# – refers to the value that appears on the cell if any of the IF conditions is met. You can set one result per argument. If there’s only one argument, you can set a result for when it is true and another when it is false (optional)

How to Assign a Number Value to Text

We’ll be using the same dataset for the last time:

This time, we’ll be using the IF function to assign numbers to the different regions.

  • Select an empty cell, preferably one that is adjacent to the array or range of cells to which we’ll be assigning numbers. For our illustration, we’ll be selecting cell C2.

  • In the selected cell, enter the formula for using the IF function. In our illustration, the formula will be =IF(B2=”North”,1,IF(B2=”East”,2,IF(B2=”West”,3,IF(B2=”South”,4)))). With this formula, if the value in cell B2 is North, the function will return a 1 value, 2 if the value is East, 3 if the value is West, and 4 if the value is South.

  • Press the Enter key to execute the formula.

  • Copy and paste the formula to the rest of the column (until the next empty row).

  • We have successfully assigned numbers to text in Excel.

Conclusion

And those are the different methods you can use to assign numbers to text in Excel.

Two of them require functions that are only available in the latest version of Excel, but there’s one that you can use for any version of Excel.

Which of the above methods do you prefer the most?

Or do you have another method that’s not mentioned in the article?

Let me know in the comments.