Extract Pattern From A Cell

Re: Extract Pattern From A Cell

Could you provide a better explanation?

If you are wanting a function that will generate the pattern itself that could be tricky!

Re: Extract Pattern From A Cell

jonny, [fa]*[/fa]

While your thread title is fine, your explanation assumes we KNOW what you want.

Re: Extract Pattern From A Cell

Not much to do with such a rough sample...

Function jonny(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "number \S+"
    .IgnoreCase = True
    If .test(txt) Then
        jonny = .execute(txt)(0)
        Exit Function
    End If
    .Pattern = "\S+{5}"
    If .test(txt) Then
        jonny = .execute(txt)(0)
    End If
End With
End Function

Re: Extract Pattern From A Cell

Quote from jonny

1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"

Hi jonny,

From your example 1, I assume the pattern is the length of characters in a word. I came up with something like this:

If A1 contains "golf car is really comfort", then by entering 4 as the length of word to return, B1 will return "golf"

Sub MatchCharLength()
Dim x As Variant, y() As Variant, Length1 As Integer


x = Split(Cells(1, 1), " ")
Length1 = InputBox("Enter the number of characters in the word to return")
i2 = 0


For i = 0 To UBound(x)
If Len(x(i)) = Length1 Then
ReDim Preserve y(0 To i2)
y(i2) = x(i)
i2 = i2 + 1
End If
Next i


For i = 0 To UBound(y)
Cells(1, i + 2) = y(i)
Next i


End Sub

Re: Extract Pattern From A Cell

Jonny, for what its worth - the SEARCH function in Excel allows for wild card matching...

so in your example, if you had that string in A1, then this formula would work...

=MID(A1,SEARCH(" ????",A1),FIND(" ",A1,SEARCH(" ????",A1)))

From Excel help:

Quote

You can use the wildcard characters, question mark (?) and asterisk (*), in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

However, this does have limitations because you need to enter the pattern twice. However, if this is what you are looking for, it would be easy enough to convert it to a UDF whereby you would call the above formula in VBA and only have to pass it one instance of the pattern you are looking for.

Also, a UDF or Formula solution will likely only return the first instance of the pattern being matched... usually left to right as the string is processed. A function by and large should not / can not return more than one answer / solution to a cell.

HTH
Ger