Hi

I have a workbook which contains addresses, but has been formatted in a bit of an odd way. The original format is along the lines of "Flat 1, 145, Any Street ¦PC1_1PC" in a single cell. I have successfully separated the post code (for non-English readers, that's the part after the ¦), reomved the underscore and put it in a separate cell.

However, I need to remove the spaces and the ¦ from the "address" part. Unfortunately, the number of spaces varies, as does the number of characters in the address. I am now stuck trying to work out how to do this - I think I need to do something with the LEN of the string, but how do I find the number of characters (I can't just count spaces as tehre will be a varied number of spaces within the actual address).

Any help much appreciated.