# Using Find to search for an Alphanumeric in a String

#### rex759

##### Well-known Member
Hello,
I am trying to find the first occurrence (row number) of an alphanumeric string starting with CA with 8 digits following it. Example, CA12345678. The string is randomly placed in the rest of the text in the cell. It could be the first or last word in the sentence but usually never the same.

The cells look something like this:

12/24/2020 Mrs. Smith Fixer Upper CA12345678 House and Windows
CA12345678 12/24/2020 Mrs. Smith Fixer Upper House
12/24/2020 Mrs. Smith Fixer CA12345678 CAB 12x26

The numbers in the string are always random and never the same
I tried using the Like function but I don’t think you can use it with the Find command. I tried using a wild character something like this:

VBA Code:
``Range("A1:A100").Find(What:="CA????????", After:=Range("A1")).Row``

Thinking that I could find the pattern of two letters followed by 8 digits as that seems to be unique in the report.

Any help is greatly appreciated.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### DanteAmor

##### Well-known Member
Find(What:="CA????????"
That could return something like "CA whateve"r it is.

Try this:
VBA Code:
``````Sub test()
Dim a, i As Long, j As Long, k As Long, m, exists As Boolean
a = Range("A1", Range("A" & Rows.Count).End(3)).Value2
For i = 1 To UBound(a)
For j = 1 To Len(a(i, 1)) - 9
If Mid(a(i, 1), j, 2) = "CA" Then
m = Mid(a(i, 1), j + 2, 8)
exists = True
For k = 1 To 8
If Not Mid(m, k, 1) Like "*[0-9]*" Then exists = False
Next
If exists = True Then
MsgBox "Fisrt row : " & i & ". String : " & Mid(a(i, 1), j, 10)
Exit Sub
End If
End If
Next
Next
End Sub``````

varios 11jun2020.xlsm
A
1but CA abc def gh
212/24/2020 Mrs. CASmith Fixer Upper CA12345 678 House and Windows
3CA1234567.8 12/24/2020 Mrs. Smith Fixer Upper House
412/24/2020 Mrs. Smith Fixer CA12345678 CAB 12x26
512/24/2020 Mrs. Smith Fixer CA1234885678 CAB 12x26
Hoja9

#### rex759

##### Well-known Member
Thank you for your response. It works perfectly!

Replies
1
Views
75
Replies
4
Views
175
Replies
17
Views
970
Replies
5
Views
413
Replies
6
Views
396

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back