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

Yes, if I were to map the parameters in the formula, how would I go about doing so? Is there somewhere this can be saved in Excel? When I mentioned saving it as a template, I'd like to have it saved similarly to when you use the equal sign to see a list of functions available in excel or when you create your own and save. That way, like you mentioned, if I were to use this same function for many different columns with different information I can just change the specific cell locations and text and values to be converted. I hope this makes sense!

Thanks!

Answer 2

This is indeed meaningful. If you need a more templated and standardized mapping technique, I recommend using a lookup table combined with the XLOOKUP function instead of the SWITCH function. Below, I will explain how to do this:

First, create a new sheet named Mapping, which contains a simple two-column table. Column A is the original text, and Column B is the corresponding numbers. This table serves as a standard for all the mapping relationships you need. In other words, column A should include ALL the original text that might appear in the mapping task.

Now, return to your data sheet. In the cell where you need to perform the mapping (e.g., A2), enter the following formula, and drag it down:

=XLOOKUP(A2, Mapping!$A$2:$A$10, Mapping!$B$2:$B$10, 0)

In this formula, A2 is the lookup value, Mapping!$A$2:$A$10 is the lookup range (the "original text" column in the mapping table), Mapping!$B$2:$B$10 is the return range (the "numbers" column in the mapping table), and 0 is the default value if no match is found (optional parameter).

This way, whenever you need to map text to numbers, you only need to maintain the "Mapping" sheet without worrying about the formula itself. You can save the workbook containing the Mapping sheet as an Excel template.

I hope this will be helpful to you.