Extract Pattern From A Cell

  • Hi, Guys.

    Needs your help or a hint, I'm trying to write 'user-defined' function in Excel, that doing following:
    Getting two arguments: cell and 'like' pattern (_ _ , *, e.t.c).
    As a result returns data that match this pattern.

    For example:
    1. Cell = "golf car is really comfort", pattern = "_ _ _ _" will return "golf"
    2. Cell = "toyota is number one", pattern = "num*" will return "number one"

    Thank you.

  • Re: Extract Pattern From A Cell

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

  • 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"

  • Re: Extract Pattern From A Cell

    Sorry for inconvenience and for my english, all of samples is pretty nice , but not really what I'm lookg for.
    I will try to explain ..

    Function will receive two arguments:
    1. some cell with a string
    2. string with/without operators : underscore (_) or asterisk (*) , where (_) is one character and (*) is more then one character.

    Example attached in a drawing.

  • 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:


    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.


    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!