we have a list from which we need to extract only the unique numbers. In one column we have the Country and then we need this and a 6-digit number contained in the next column

we have achieved this using two ways

  1. Using Merging both columns + Right Function ( =A2&” “&RIGHT(B2,6)

we have 181 Account Numbers with Countries in Column A we used A2& ” ” for Spaces between Country & Account Number Like Belgium 302160 & Right Function with References from B2 & we want to extract 6 Characters from the Right side of the Account Number.


Merge+Right Function

Using Unique Function

2nd method to Get Unique Numbers from two Different columns is using Unique Function.

Syntax :

=UNIQUE(A2:A181&” “&RIGHT(B2:B181,6))

Unique & then Select First Column A2 To end of the Column A181 than & Character to add a Space with ” ”

& Using Right Function on Column B2 Selecting the Whole Common From B1 to B81 ,

6 at the end of the formula means Get 6 Characters from B2 starting from the Right Side. If you need more details about Left, Middle & Right Functions Click Below

Right Function in Excel 

Left Function in Excel 

MID Function in excel 

Here are the results:

unique Function in excel

Download Sample File

The Excel UNIQUE function returns a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.


=UNIQUE(array, [by_col], [exactly_once])


The UNIQUE function extracts unique values from a range or array:

=UNIQUE({"F";"G";"H";"F";"S"}) // returns {"F";"G";"H""S"}