test for text

[kin]

Hello

I d like to test for the existence of text in range D5 to D10000 for example

I may have this 49000897890VGN21SP. VGN21SP is text so I d like to spot it

it may also exist on its own VGN21SP can you suggest me with a macro?

like this one

Sub DeleteNAs()
Dim C As Range
For Each C In ActiveCell.CurrentRegion

If C.Text = "#N/A" Then C.ClearContents

Next C
End Sub

 

[Andrew Poulsom]

Like this?

If Instr(1, C.Value, "VGN21SP") > 0 Then

 

[kin]

i d like to find these case with a macro and put it to personal workbook

thanks

 

[Richard Schollar]

What do you want to do with these values once you find them? Also, in which ranges may they appear eg only in column A, B:D or potentially anywhere on the sheet?

 

[Andrew Poulsom]

Like this?

Sub DeleteText()
    Dim C As Range
    For Each C In ActiveCell.CurrentRegion
        If Instr(1, C.Value, "VGN21SP") > 0 Then C.ClearContents
    Next C
End Sub

<!-- / message --><!-- sig -->

 

[kin]

Alas it cantt be only VGNSP but any other text and my range is D5: D10000

 

[Richard Schollar]

What do you want to do with any such text values when you find them? Are the values in this range that don't contain text genuine numeric values?

 

[kin]

to color them , I dont understand your other question

 

[kin]

yes they ar numbers i convert thme to general or number with no 2 digits (0s) in the end

 

[Richard Schollar]

You could just use conditional formatting to color them? Select your range (in this instance D5:D10000 - easy way to do this is to hit F5 and type in D5:D10000) and go Format>Conditional Formatting and change to Formula Is in the left hand dropdown and use the following formula:

=NOT(ISNUMBER(D5))*(D5<>"")

and then hit the Format and select your color of choice.