VBA to remove characters from a string

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.

Maybe?

Please Login or Register  to view this content.

If none of the above suggestions work for you, submit an example workbook with a couple of before and after examples.

As a last resort you could try saving the file as a csv file as this is a “flat file” with no formatting.

Then import the file back into excel and see if things have improved.

Alf