How to find duplicate words in a cell in excel with example?
To find a duplicate value or word in a cell in excel we can use IF function with SUBSTITUTE function. Now suppose a data contains addresses and we want to know either that data contains duplicate word in a cell or not, so we will use IF with SUBSTITUTE.
We have a file which contain some data of State, City and Addresses.
We wants to find is there any duplicate city name exist in address column or not.
Now in cell D2 we will type formula as =IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups") and fill the formula till the end of the data.
In D column there is remark "Dups" for the addresses which contain city name twice.
Explaination:- In Formula (=IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups")), First the SUBSTITUTE function replace the city name (city name on the second position, will not replace city name which is on first place because in SUBSTITUTE function we provide the Instance No as 2) which is in city column (B Column) and if substitute the result will match with C column data it means no changes will occur means no city name is on second place in address data but if the substitute result will not match with C column data it means the city name on second position is exist. So if substitute result will not match with C column data there is a remark dups will be filled in D column else it will remain blank.
To find a duplicate value or word in a cell in excel we can use IF function with SUBSTITUTE function. Now suppose a data contains addresses and we want to know either that data contains duplicate word in a cell or not, so we will use IF with SUBSTITUTE.
We have a file which contain some data of State, City and Addresses.
Now in cell D2 we will type formula as =IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups") and fill the formula till the end of the data.
In D column there is remark "Dups" for the addresses which contain city name twice.
Explaination:- In Formula (=IF(SUBSTITUTE(C2,B2,"",2)=C2,"","Dups")), First the SUBSTITUTE function replace the city name (city name on the second position, will not replace city name which is on first place because in SUBSTITUTE function we provide the Instance No as 2) which is in city column (B Column) and if substitute the result will match with C column data it means no changes will occur means no city name is on second place in address data but if the substitute result will not match with C column data it means the city name on second position is exist. So if substitute result will not match with C column data there is a remark dups will be filled in D column else it will remain blank.
0 comments:
Post a Comment