Removing punctuation in a formula

[williscm]

Hi all! I'm new and need help, so thanks very much for allowing me to post!

I have a database called a "Land Sales Database" (LSD) in Excel, and a separate file to achive all files. I have the following formula in the archive file:

=UPPER(IF(+C7="","",VLOOKUP(+C7,'G:\Registration\Land Sales\Landsales\MANUAL\System\[Land Sales v2006.xls]Data'!$A:$B,2)))

The formula returns a name result from LSD, ie:

JOE B. ROWN, "WAGGA", 13 TREVOR ST., BRISBANE

Is there a way to modify the above formula to remove ALL punctuation, so the desired result would be:

JOE B ROWN WAGGA 13 TREVOR ST BRISBANE

Any ideas?

Regards
Chris

[martindwilson]

use a few substitutes (note you need 4 " , """" if substituting " s)
=trim(substitute(substitute(substitute(upper(vlookup(a1,c1:d3,2,false)),"""",""),".",""),",",""))

[williscm]

I got it, but my head exploded. So many parentheses ...! Thanks for that.