in address column two time same address in single-cells so I need to remove duplicate in single cell-CodePudding

address
10 Plaza Square 2nd Floor
10 Plaza Square 2nd Floor 10 Plaza Square 2nd Floor
10 Plaza Square 2nd Floor 10 Plaza Square 2nd Floor

In address column two time same address in single-cells so I need to remove duplicate in single cells.

CodePudding user response：

I used the following formula for applying conditional formatting:

=COUNTIF(A$2:A$5,A1)>1

This colours every item which has duplicates in row "A", which makes it easy to see what I need to delete.

CodePudding user response：

Setup a 'helper' column to determine the length of the Cell containing the address. Then use the a formula in the next column to do some calculations.

In Column A you have your Addresses. Your Helper Column Formula would be:

=LEN(A1)/2

You can copy that formula all the way down your table.

Next, the calculating column will provide you what you need:

=IF(LEFT(A1,B1)=RIGHT(A1,B1),LEFT(A1,B1),A1)

Reference

From here, you can then select column with fixed address, copy them, and then use Paste Special options to paste only the Values into the Address Column.