Hello Excellers and welcome back to another #formulafriday #exceltip in my Excel tip series. Today I want to share with you a question I was asked by a subscriber. They need to generate unique ID numbers or references, from their data set. Or to explain more take the first three letters of the location of the account (in this case the Country) name, and then incrementally generate numbers for the accounts.
You can see a sample of a similar data set and the expected outcome that is required in the screenshot below.
So, just how can we achieve this result. It’s rather easy. We are going to use three formulas or methods to do do this. W. start with the CONCATENATE Function, the LEFT Function, and the COUNTIF Function. By combining these three awesome methods the unique ID is a breeze. Let’s get started.
Let’s Begin The Formula!
PART 1. We start off using the CONCATENATE.
This will join several strings together into one text string. This sounds like what we need. The syntax of CONCATENATE is simple and has only one required argument.
The subsequent arguments are optional, hence the [ square] brackets.
=CONCATENATE(text1, [text2], …)
So, the first part of the formula will join the first three letters of the City contained in cells in column B. How do we get those letters?.
PART 2. We can use the LEFT Function.
Here is a quick recap of LEFT and it’s syntax.
The LEFT Function in Excel extracts a given number of characters from the left side of a supplied text string.
The syntax for this formula is
=LEFT(text, [num_chars])
Where
text is the string you want to extract from (that is the cell reference that holds your text string)
num_chars is the number of letters you want to extract from the full string. Note the [ ] brackets. This means that this is an optional argument, which if left out default the argument to 1.
So, in our formula, we want to take the first three (3) letters of the City Names in Column B with the header ‘City’. That is straightforward. we already have the first part of our formula, and the LEFT function becomes the first argument as below
=CONCATENATE(LEFT(B3,3),
PART 3. Add a ‘dash’ (-) to the Unique ID.
We can easily do this by inserting another argument in the CONCATENATE function.
=CONCATENATE(LEFT(B3,3),”-“
PART 4. Generate A Unique Count Of City
We now need to generate a unique last number for the end of the ID. So the fourth argument we use in the CONCATENATE formula is COUNTIF. We will use this to count how many times incrementally the City names in Column B appear. Cool huh?. COUNTIF uses a very simple syntax as there are only two arguments.
=COUNTIF(range, criteria)
The two arguments are:-
range – defines one or several cells to count. You put the range in a formula like you usually do in Excel.
criteria – defines the condition that tells the function which cells to count. This argument can be a number, text string, cell reference or expression.
So, in this example, we want Excel to count as it travels down the column of data how many times each city appears in the data. For example the first time the City London appears we want Excel to place 1 in our cell. The second time it appears we it to count and put 2 in the cell and so on and so forth. How do we do this?. Easy. We make the criteria that Excel uses dynamic.
To complete the formula for our first cell, we use the formula
=CONCATENATE(LEFT(B3,3),”-“,COUNTIF($B$3:B3,B3))
Where the range of the COUNTIF formula is $B$3:B3,B3. By anchoring the first cell of the range, the range which Excel analyses is expanded as we drag the formula down the column of cells. So, the first time Excel looks for LONDON the range of cells it is referencing is just the one, hence it generates 1. By the time it finds the second entry of LONDON the range it is looking in has been expanded to $B$3:B7,B7, and so on and so forth.
You can see this demonstrated clearly in the screencast below. The green arrows indicate the expanding range of cells.
So, now we have the unique ID for each customer record by combining CONCATENATE, LEFT and COUNTIF Functions. A nice solution don’t you think?.
More Excel Articles.
