Excel VBA UDF to find and substitute substring, with wildcards, from text string. Help

Looking everywhere, I haven't been able to find an Excel VBA user defined function to find and replace a substring, with wildcards, from text string.

Something like, Example 1:

A1: "THIS IS THE ORIGINAL STRING"

Function in A2: NewSubstitute(A1,"O*L","NEW")

Result in A2: "THIS IS THE NEW STRING"

Example 2:

A1: "THIS IS THE ORIGIN, ORIGINAL, ORIGINALE, STRING"

Function in A2: NewSubstitute(A1,"O*,","NEW")

Result in A2: "THIS IS THE NEW NEW NEW STRING"


Help MUCH appreciated! Thanks!!!!!!

Originally Posted by infottico

A1: "THIS IS THE ORIGINAL STRING"

Function in A2: NewSubstitute(A1,"O*L","NEW")

Result in A2: "THIS IS THE NEW STRING"

A1: "THIS IS THE ORIGIN, ORIGINAL, ORIGINALE, STRING"

Function in A2: NewSubstitute(A1,"O*,","NEW")

Result in A2: "THIS IS THE NEW NEW NEW STRING"

Please Login or Register  to view this content.

Wow, works perfectly!!!!!!! Thanks for the Quick reply!!!!!

Another variant with Regex

Please Login or Register  to view this content.

This variant (JEC) did not work, as New Subtitle, supplied by jindon, did.

A3: "A02AB05, A12AA12, A16AX05, B01AA08, B05XA08, B05XA17, L03AX13, V03AE04"

A2: NewSubstitute(A3,"A1*,","") -> "A02AB05, B01AA08, B05XA08, B05XA17, L03AX13, V03AE04" (as required)

A2: JEC(A3,"A1*,","") -> "A02AB05, A12AA12, A16AX05, B01AA08, B05XA08, B05XA17, L03AX13, V03AE04" (did not work)

You don't need the * in my UDF. Forgot to tell you, my bad
Now I see more examples, it will fail in other cases too.

=JEC(A3,"A1,","")

Please Login or Register  to view this content.

Please Login or Register  to view this content.