Using Find to search for an Alphanumeric in a String

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
530
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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


1591911389097.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top