Are you struggling with generating unique IDs for the names in your Excel column? Do you have a list of employee names and need to assign a single unique number to each name in the list? If so, you’ve come to the right place.
In this post, we’ll show you how to assign a number value to a word using a formula in Excel.
First, let’s take a look at our sample sheet with employee names. We want to assign a unique ID to each name in the list. For example, the name “SawStop” should have an ID of 1, and so on. When the name is repeated, the ID should also be repeated.
To assign ID 1 to the first name in cell A2, we need to check whether the name in the current row is the same as the previous row. If it is, we’ll use the ID from the previous row. Otherwise, we’ll increment the ID by one.
To achieve this, we’ll write the following formula in cell A3:
=IF(B3=B2,A2,A2+1)
This formula checks whether the name in cell B3 is the same as the name in cell B2. If it is, it uses the ID from cell A2. Otherwise, it adds one to the ID from cell A2 to get the new ID.
We’ll copy this formula down to the end of the list, and we’ll see that the ID changes to 2 when the name in cell B5 is different from the name in cell B4.
And there you have it! You now know how to assign a number value to a word using a formula in Excel.
We hope you found this post helpful. If you have any questions or comments, please leave them below.