search within string

[Dave B]

I'd like to see if a cell contains only "-" and " " (space) characters.
So I'd like to identify " -----" or " - - - - ", but not " --a- ".
I figure I could look one character at a time for something other than
" " or "-". But this seems tedious. Is there an easier way? Thanks.

[Bob Phillips]

=LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))=0

If TRUE it only contains those characters

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave B" <[email protected]> wrote in message
news:[email protected]...
> I'd like to see if a cell contains only "-" and " " (space) characters.
> So I'd like to identify " -----" or " - - - - ", but not " --a- ".
> I figure I could look one character at a time for something other than
> " " or "-". But this seems tedious. Is there an easier way? Thanks.
>

[Leith Ross]

Hello Dave,

Here is an alternative method if you need to search a large range of cells quickly. The macro examines only cells that contain true strings and loops through each byte of the string. The compare is speeded up by using an API call to place the bytes of the string (cell contents) into an array. The bytes are then compared to the "-" and " " characters. There is a match if the count of spaces and dashes equal the length of string. You would need to insert whatever code you want to run at this point in the macro (its marked in green).


To Use the Macro:
FastCheck "A1:Z300"
Replace "A1:Z300" with the range you want to check

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Tom Ogilvy]

Lightly tested, but code like

Sub ABC()
Dim s As Single, e As Single
sStr = "=SUM(IF(LEN(SUBSTITUTE(SUBSTITUTE($A$2:$F$29,""-"",""""),""
"",""""))=0,1,0))"
s = Timer
For i = 1 To 10000
res = Application.Evaluate(sStr)
Next
e = Timer
Debug.Print e - s
End Sub

seemed to be fairly consistent regardless of the content of the cells.

Your code was 1/3rd faster with only 3 cells containing text, but when all
cells contained text (17 characters), it was about 2.5 times slower.

You don't need to use the API to create a byte array from a string. Also,
this uses special cells to eliminate looping through all cells.

for the range with only 3 text cells, it was 5 times faster than your
original. For the range with all text strings, it was slightly faster (about
2 seconds on 10000 iterations). Recognize that time only has about 1 sec
resolution, so don't offer these as definitive, but as orders of magnitude.

Sub FasterCheck()

Dim Bytes As Long
Dim ByteArray() As Byte
Dim Cell
Dim MyStr As String
Dim N As Long
Dim s As Single
Dim e As Single, k As Long
s = Timer
For k = 1 To 10000
For Each Cell In Range("A2:F29").SpecialCells(xlConstants, _
xlTextValues)
MyStr = Cell.Value
Bytes = Len(MyStr) + 1
ByteArray = MyStr

For Each B In ByteArray
If B = 32 Or B = 45 Then N = N + 1
Next B

If N = Bytes Then
'Data is good - add whatever routines you need here
End If

N = 0
Next Cell
Next k
e = Timer
Debug.Print e - s
End Sub

--
Regards,
Tom Ogilvy



"Leith Ross" <[email protected]> wrote
in message news:[email protected]...
>
> Hello Dave,
>
> Here is an alternative method if you need to search a large range of
> cells quickly. The macro examines only cells that contain true strings
> and loops through each byte of the string. The compare is speeded up by
> using an API call to place the bytes of the string (cell contents) into
> an array. The bytes are then compared to the "-" and " " characters.
> There is a match if the count of spaces and dashes equal the length of
> string. You would need to insert whatever code you want to run at this
> point in the macro (its marked in green).
>
>
> TO USE THE MACRO:
> FastCheck "A1:Z300"
> Replace "A1:Z300" with the range you want to check
>
>
> Code:
> --------------------
> Private Declare Sub CopyMemory _
> Lib "Kernel32.dll" _
> Alias "RtlMoveMemory" _
> (ByRef lpvDest As Any, _
> ByRef lpvSource As Any, _
> ByVal cbCopy As Long)
>
>
> Sub FastCheck()
>
> Dim Bytes As Long
> Dim ByteArray() As Byte
> Dim Cell
> Dim MyStr As String
> Dim N As Long
>
> For Each Cell In Range("A1:F93")
> If VarType(Cell) = 8 Then
>
> MyStr = Cell.Value
> Bytes = Len(MyStr) + 1
> ReDim Preserve ByteArray(Bytes)
>
> CopyMemory ByteArray(1), ByVal MyStr, Bytes
>
> For Each B In ByteArray
> If B = 32 Or B = 45 Then N = N + 1
> Next B
>
> If N = Bytes Then
> 'Data is good - add whatever routines you need here
> End If
>
> N = 0
> End If
> Next Cell
>
> End Sub
>
> --------------------
>
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=478704
>

[Leith Ross]

Hello Tom,

I appreciate you taking the time do a benchmark test. I stand corrected on my code being faster. I'll add your other tips and changes to my toolbox.

Thanks,
Leith Ross