Hi all,

brand new member of the site with a bit of a puzzler. Not sure where to start or indeed if this is the place to ask!!

In a nutshell I have a spreadsheet of data that i need to go through to find data that meets a specific criteria and I am not sure where to start.

Best example i can give is a telephone number.
Rather than search the spreadsheet for a specific number i want to be able to get Excel to show me all instances of an eleven digit number regardless of what the actual number is.

Hopefully that makes sense?

Cheers

D.

play with this vba code. changes all the mobile number contained cells background to red color

__________________________________________________ ___________

' created and edited by Bala Sesharao
Sub find_mobile()
Dim x_cordinate, y_cordinate As Variant
Dim resultant As String

x_cordinate = 1
y_cordinate = 1
Sheets("sheet1").Cells(1, 1).Select
For x_cordinate = 1 To 65000
For y_cordinate = 1 To 256
resultant = Sheets("sheet1").Cells(x_cordinate, y_cordinate)
If Len(resultant) = 11 And IsNumeric(resultant) Then
Sheets("sheet1").Cells(x_cordinate, y_cordinate).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next y_cordinate
Next x_cordinate



End Sub

Private Sub Workbook_Open()
Call find_mobile
End Sub

____________________________________

the logic in the above code
1) find the numeric cells in the sheet1 (change the sheet name as required)
2) evaluate all the numeric cells with length = 11 character i.e mobile number

If column A is where are all the numbers a

=IF(LEN(A2)=11,"Mobile Number","Not Mobile Number")