Hi,

This is my first question on this forum.

I am not very good in describing the question so please excuse me for the same.

I have set of values in a column in excel given below.

Ac Acid
Ac Acid
Ac Acid
Ac Acid
Ac Acid

If i copy paste this cell in another tab then it copies fine.But if i click on the cell copy the contents it is copied in the format given below ( contains extra line feeds)

Ac Acid

Ac Acid

Ac Acid

Ac Acid

Ac Acid

i need to compare these vales and there are lots of them so i cant manually go and remove extra line feeds and the comparison fails due to the invisible extra line feed.

Please help.

Thanks in advance.

i have used =SUBSTITUTE(A1,CHAR(10)&CHAR(10),CHAR(10)) that this will only work if i click the cell and copy inside contents in another cell this is not working on the original cell ( eg 1) i have 500 cells so clicking them and copying them will be a huge task.