I have AplphaNumeric part numbers that sometimes contain hyphens or decimals. In order to make them more searchable (since people don't always use the hyphens or decimals properly when they search) I would like to create all the possible variants of the part number, but with one formula.
So AHW18.787 becomes AHW18 787 and AHW18787 with
=SUBSTITUTE(A1,"."," ") and
=SUBSTITUTE(A1,".","") respectively, and
AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with
=SUBSTITUTE(A1,"-"," ") and
=SUBSTITUTE(A1,"-","").
What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command?
Thx
So AHW18.787 becomes AHW18 787 and AHW18787 with
=SUBSTITUTE(A1,"."," ") and
=SUBSTITUTE(A1,".","") respectively, and
AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with
=SUBSTITUTE(A1,"-"," ") and
=SUBSTITUTE(A1,"-","").
What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command?
Thx