I need to manipulate strings and substrings in the active cell of a
spreadsheet. To do this, I need to cleanup extraneous characters in a string
to get them in a standard format. Since users enter the data manually,
cleanup is a must. Here is some problem code that should be self-explanatory
in the comments. It is greatly simplified to make sure it works before I
begin expanding it.

Code is at workbook Module level. Any help or other code routes appreciated.
Same results for Excel 2003 and 2007.

Option Compare Binary
Public Sub periodStripper()
' ActiveCell on spreadsheet formatted as "Text"
' See comment #4. In ActiveCell, want to replace any
' continuous substring of periods until only 1 remains.
' "..." or "...." would become "."
' or "...3a...2[]" would become ".3a.2[]"

Dim myStr As String
myStr = ActiveCell.Value
myStr = Trim(myStr) ' #1 trim lead/trail spaces. works OK.
myStr = Replace(myStr, " ", "") ' #2 remove any spaces. works OK.
myStr = Replace(myStr, ".[", "[") ' #3 delete period next to left bracket.
works OK.
' #4 following fails. should keep replacing any 2 periods until only 1
remains.
' Have also tried putting in a Do Loop.
myStr = Replace(myStr, "..", ".")
ActiveCell.Value = myStr
' String and results
' "....2A4." doesn't strip any periods
' "2.......[a]" strips 1 of 7 periods
' "...3a...2[]" strips 1 period from each group "..3a..2[]"
' same result with Excel 2003 and 2007
End Sub

Public Sub testStrLen()
' want to see if Period at end of string
' problem with Len and inStr
' ActiveCell Examples
' "2A4." 4 chars long, period at end
' "....2A4." 8 chars long, period at end
' "2.......[a]" 11 chars long, no period at end
Dim myStr As String
Dim myStrEnd As Integer
myStr = ActiveCell.Value
myStrLen = Len(myStr)
MsgBox ("Data: " & myStr & _
" -- Length: " & Len(myStr) & _
" -- inStr Value: " & InStr(myStrLen, myStr, "."))
' Start String followed by values that Len and InStr return
' "2A4." --- 4 chars long, period position 4 (Is Correct)
' "....2A4." --- 6 chars long, period position 6 (both wrong)
' "2.......[a]" --- 7 chars long, period position 0 (Len wrong)
' same result with Excel 2003 and 2007
End Sub