Len and InStr problem manipulating strings

F

facmanboss

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
 
R

Ron Rosenfeld

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


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
Click to expand...


--ron
 
F

facmanboss

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 said:
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, "..", ".")
Click to expand...


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
Click to expand...


--ron
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...

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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding string and color the found string 1
unable to get the activate property of the range class 6
VBA Export to PDF 0
Editing text copied into a cell by a UserForm 2
type mismatch error in macro in excel 0
Prompt For Replace in Selection And More... 0
For...Next loop problem!! 5
copy selected cells to new sheet. 12

Top