Excel 2003/2007 Problem with InStr, Replace, and Len

I have run into a brick wall with InStr, Replace, and Len. I take the data from the active cell (formatted as text). I need to cleanup the data and do further massaging of it. In testing cleanup code, I get behavoirs and results I simply don't understand and can't figure out from hours searching forums many places.

The following simplified example commented code can be cut/pasted into a new spreadsheet module and should be self-explanatory. Example data is in the comments. All help greatly appreciated.

Data is entered by multiple users and multiple sources. Therefore there is no control over the input quality so extraneous characters and multiple repeated periods and spaces can show up anywhere.

Please Login or Register  to view this content.

This might help

Please Login or Register  to view this content.

A string of periods can get automatically converted to an Ellipsis which is ASCII character 133.

Welcome to the forum.

Try this:

Please Login or Register  to view this content.

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes