Test if cell contains certain text, text that contains parentheses.

[dee_ln]

Hi all,

I have a sheet like this (sorry about all the borders):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 131"]
<tbody>[TR="class: grid"]
[TD](Nobody) (0)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 72"]
<tbody>[TR="class: grid"]
[TD]sometext
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 131"]
<tbody>[TR="class: grid"]
[TD]Steve Smith (888)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 72"]
<tbody>[TR="class: grid"]
[TD]attribute 1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 131"]
<tbody>[TR="class: grid"]
[TD]Jane Smith (787)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 72"]
<tbody>[TR="class: grid"]
[TD]sometext
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 131"]
<tbody>[TR="class: grid"]
[TD](Nobody) (0)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 72"]
<tbody>[TR="class: grid"]
[TD]sometext
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


In VBA, I need to copy the rows that do not contain “(Nobody) (0)” to another sheet.

EXAMPLE1:
If Worksheets("Sheet1").Cells(i, 1).Value <> "(Nobody) (0)" Then
Worksheets("Sheet1").Rows(i).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & rowcounter)
Etc.

EXAMPLE2:
If Worksheets("Sheet1").Cells(i, 1).Value <> "Steve Smith (888)" Then
Worksheets("Sheet1").Rows(i).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & rowcounter)
Etc.

Example 1 does not work, it does not recognize the value (Nobody) (0) in a cell. It copies all rows.
Example 2 does work, it copies all rows that do not contain Steve Smith (888) in a cell.

I need example 1 to work, So I guess the code needs extra quotes or something around (Nobody) (0)

Any suggestions would be greatly appreciated!

 

[Snakehips]

dee_in,

Welcome top Mr Excel.

If your cells contain the string '(Nobody) (0) then I can see no reason why Example 1 should not work.

Do you have leading or trailing spaces or any other 'invisible' control characters in with that string?
Do a test formula on the string cell e.g. if string is in A1.... =LEN(A1) and see if the answer is 12.

 

[dee_ln]

Hi. Thank you for answering.
Indeed the cells seem to contain some invisible characters as evidenced by the LEN test.

This data was imported from a Sharepoint export.
I'll have to find an other way to test. The sheet contains more columns, so I'll try testing on those.

Thanks.

 

[Snakehips]

Try incorporating something like below in order to clean up the cells(i,1) data before testing.

'EXAMPLE1:


With Worksheets("Sheet1").Cells(i, 1)
' remove any trailing and leading spaces and invisible control characters
.Value = Evaluate("SUBSTITUTE(TRIM(CLEAN(" & .Address & ")),CHAR(160),"""")")
If .Value <> "(Nobody) (0)" Then
Worksheets("Sheet1").Rows(i).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & rowcounter)
'Etc.
End If
End With

Hope that helps.

 

[dee_ln]

Thanks Tony! I will try that out.