Len and InStr problem manipulating strings
 

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

Len and InStr problem manipulating strings
 

See inline comment:


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, "..", ".")


Try this instead:

======================================
Do Until myStr = Replace(myStr, "..", ".")
myStr = Replace(myStr, "..", ".")
Loop
=============================



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


--ron

Len and InStr problem manipulating strings
 

Ron,
Thanks but I had already tried that Do Loop. I tried yours just to make
sure. Same behavoir. Doesn't work.

Here is an example:
"€¦.2..A€¦.4." becomes "€¦.2.A€¦.4."
Only the TWO periods Between "2" and "A" get replaced. No other combo does.

Also,
I still don't get the InStr behavoir in one of my examples below.
Any more help appreciated. I'm at a deadend.
Thanks.

"Ron Rosenfeld" wrote:

See inline comment:


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, "..", ".")


Try this instead:

======================================
Do Until myStr = Replace(myStr, "..", ".")
myStr = Replace(myStr, "..", ".")
Loop
=============================



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


--ron

Len and InStr problem manipulating strings
 

On Sun, 22 Feb 2009 06:04:01 -0800, facmanboss
wrote:

Ron,
Thanks but I had already tried that Do Loop. I tried yours just to make
sure. Same behavoir. Doesn't work.

Here is an example:
"….2..A….4." becomes "….2.A….4."
Only the TWO periods Between "2" and "A" get replaced. No other combo does.

OK, I'm pretty sure I understand what is going on.

The issue is that there are NOT two *periods* prior to the "2" or between the
"A" and the "4".

What you are seeing is an ellipsis -- (ASCII code 133) plus a period. It may
look like 3 periods (4 all together), but it is only 2 characters: ASCII code
133 followed by a "."


Also,
I still don't get the InStr behavoir in one of my examples below.
Any more help appreciated. I'm at a deadend.
Thanks.

I suspect the same problem with your interpretation of your InStr behavior. The
ellipsis may look like three periods, but it is only one and will not be
recognized by code that is looking for a "period".

You should be able to make appropriate changes now.
--ron