Extracting data based on an alphanumeric pattern

[thedoveoflove]

Hi All

I have a column of data that contains text such as 'as per A3', 'B4 requested' and 'as per F6 Mark' (these aren't cell references).

I'm trying to find a formula that will just strip out the 'A3', 'B4' and 'F6' element of the text. I've tried SEARCH with wildcards but it's not working, and can;t use the LEFT RIGHT or MID functions due to the inconsistent data.

Any ideas at all?

Thanks

 

[István Hirsch]

Press Alt+F11, copy the code into an empty module, check Microsoft VBScript Regular Expressions 5.5 option_
at Tools/References then use this as a User Defined Function in the next column

Function DeleteAddress(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\b[A-Z]+[[\d]+\b"
        If .Test(c) Then DeleteAddress = Application.Trim(.Replace(c, ""))
    End With
End Function

 

[Rick Rothstein]

I have a column of data that contains text such as 'as per A3', 'B4 requested' and 'as per F6 Mark' (these aren't cell references).

I'm trying to find a formula that will just strip out the 'A3', 'B4' and 'F6' element of the text.

Give this UDF a try...

Function GetAddress(S As String) As String
  Dim X As Long, Words() As String
  Words = Split(S)
  For X = 0 To UBound(Words)
    If Not Words(X) Like "*[!A-Z0-9]*" And Words(X) Like "*[A-Z]#*" And Not Words(X) Like "*#[A-Z]*" Then
      GetAddress = Words(X)
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetAddress just like it was a built-in Excel function. For example,

=GetAddress(A1)

 

[thedoveoflove]

That works a treat - thanks for your help!

 

[István Hirsch]

Sorry, I misunderstood the task. Hope this can extract one or more address per cell.

Function ExtractAddress(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\b[A-Z]+[[\d]+\b)|.+?"
        If .Test(c) Then ExtractAddress = Application.Trim(.Replace(c, "$1 "))
    End With
End Function