Creating a function to convert a word to a numerical value

Question

Hi! I am trying to do a few things here...

I am trying to figure out which function to use to output text as a numerical value. For example, I am using a large worksheet with many cells that will contain word values based on responses such as race, ethnicity, sexual orientation, gender etc. that need to be changed to a numerical value like 1,2, or 3 and each answer response might have the same numerical value. For example, if someone answers their race as White, that would =1 and if they are female, that would also =1. My question is which function to use and how can I create a nested function for multiple different text answers to output as numerical values in a large worksheet?

I've already tried using the If/Ifs function which seemed to work but then I would only be able to do that one function for one answer response when there may be different responses in one column. When attempting to nest the function it does not work correctly and it doesn't seem like I can do the same function with a different text as the value, in the same column. Apologies if that doesn't make sense!

This post is not allowing me to include a screenshot.

Thank you in advance!

Answer 1

Hi CC_411,

Thanks for visiting Microsoft Community.

For your question, the SWITCH function is a very suitable solution. It can map multiple text values to corresponding numerical values, and its syntax is concise and intuitive. Here is the specific usage:

=SWITCH(text_cell, "text1", value1, "text2", value2, ..., default_value)

Assuming your responses are in column A (e.g., "White", "Female", etc.), you can use the following formula in column B:

=SWITCH(A2, "White", 1, "Female", 1, "Black", 2, "Male", 2, 0)

• If A2 is "White" or "Female", it returns 1.
• If A2 is "Black" or "Male", it returns 2.
• For other cases, it returns 0 (default value).

Notes:

• Ensure that the text values' case and format match those in the formula.
• If there are many mapping relationships, it is recommended to use VLOOKUP or XLOOKUP in combination with a mapping table.

I hope this method helps you complete your task efficiently! If you have any other questions, feel free to ask.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

This helped so much! Thank you!

Answer 3

I do have another question, is there a simple way to save this function as a template in excel? It will be used repeatedly and an easy way to access it for quick and frequent use would be helpful!

Thank you!

Answer 4

I'm not entirely sure about your specific needs. In fact, functions in Excel are used as a kind of "template" where you only need to modify the corresponding parameters.

I guess you might find it difficult to understand or lack readability when manually entering the parameters in the formula? Perhaps you could consider mapping those parameters in the formula, i.e., the text and the values to be converted, to specific cell locations, such as in the image in the reply above, changing the parameters in the formula to their corresponding positions in the table, for example, changing White to A2, 1 to B2, and so on. This way, to modify the effect of the formula, you only need to change the values in the corresponding cells without editing the formula itself.