Using Find to search for an Alphanumeric in a String

[rex759]

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:

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.

 

[DanteAmor]

Find(What:="CA????????"

That could return something like "CA whateve"r it is.

Try this:

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
1	but CA abc def gh
2	12/24/2020 Mrs. CASmith Fixer Upper CA12345 678 House and Windows
3	CA1234567.8 12/24/2020 Mrs. Smith Fixer Upper House
4	12/24/2020 Mrs. Smith Fixer CA12345678 CAB 12x26
5	12/24/2020 Mrs. Smith Fixer CA1234885678 CAB 12x26
Hoja9

 

[rex759]

Thank you for your response. It works perfectly!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.