I have the following problem I have Excel 07 I have a huge list of accounts that are repeated in the format of 0xxx,0xxx,0xxx,0xxx 4 times exactly basicaly I can make the "," into any thing I want if that helps what I wanted to know is how I can remove the repeats but keep at least one of them for me.

I have tried converting the data and putting it into a notepad making it into a .csv which makes the data with the repeats into rows which I can delete easily and get just one row but that rows "0" dissappears which makes the data useless again this happens eveytime I open the file.

I really need these accounts has anyone got any suggestions etc, thanks.....

You can extract the first four characters from the cells containing the account numbers with

Thanks for sharing this even i was looking for the same

thanks for sharing weejas im trying to get the code to work for all the values in the spreadsheet i. if i have 557 accnumbers I put the code into B1 and make it extract from A1 like =LEFT(A1, 4) but this mean i have to manually input the e.g. A1,A2 cell feilds or is there a quicker way of doing it thanks...

Once you've got the formula into the top cell in the column, select it and all the other cells in the column that need it. Then press control+d to copy it down as far as you've selected.

thank you again weejas working fine now

You're welcome!