Formula to Replace Wildcard Characters in a String

[Bob_W]

So, based on a good amount of previous searching I've almost created a nested formula which does what I need, but I am currently falling at the last fence! Hopefully someone here can help out.

I am trying to remove all non-alpha characters from a text string and through a huge nest of SUBSTITUTE functions I have removed all the numerical, punctuation, double byte characters and spaces, from my starting data to get to a string with just alpha-characters and those which excel considers wildcards too (? and *).

I have also discovered that with a combined REPLACE/SEARCH function I can remove the first instance of a wildcard but where there are multiple instances I'm stumped!! Any ideas??

Example of original string:
"TAMINITRASFORMATORIS.R.L.?33VIAEMILIAN.3720077MEL?"

Example string after cleaning:
"TAMINITRASFORMATORISRL?VIAMILIANMEL?"

=REPLACE(A2),SEARCH("~?",A2),1,"") will remove the first instance of ? but not the second.

 

[Eric W]

Maybe:

=SUBSTITUTE(A2,"?","")

 

[Bob_W]

Sadly not Eric. SUBSTITUTE doesn't work with wildcards, there don't seem to be many functions that do.

REPLACE and SEARCH are the only two i've found so far that do and for excel not to treat it as a wildcard, you need the ~ preceding the ?.

 

[jtakw]

Hi,

Don't see why Eric's formula won't work:

Excel 2010
	A	B	C
1	TAMINITRASFORMATORISRL?VIAMILIANMEL?		TAMINITRASFORMATORISRLVIAMILIANMEL
Sheet19

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(A1,"?","")

 

[jtakw]

Another sample:

Excel 2010
	A	B	C
1	TAMINITR*ASFORMATORISRL?VIAMILIA*NMEL?		TAMINITRASFORMATORISRLVIAMILIANMEL
Sheet19

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(SUBSTITUTE(A1,"?",""),"*","")

 

[Bob_W]

I stand corrected!

Not sure why or how, but at some point in the last year I convinced myself that didn't work and have been looking for alternative ways to do the substitution. Lesson here is to always recheck your workings!

What a gigantic waste of time that was!!

Thanks for your help enlightening me!

 

[jtakw]

You're welcome.