Getting a clean part number

[Rick5k]

I have a need to clean/remove dots, dashes, and slashes from a series of part
numbers. The numbers look something like the following: AI246-34-D/34 I
need it to look like AI24634D34 I can use the Substitute function to get rid
of the dashes or get rid of the slash, but when I try to combine them and get
rid of both I get an error on the formula. Some of the part numbers, I am
using numbers here loosely when these are actually text, have dots and dashes,
and some of the slashes are right not only left slahes. I'm sure that there
are other charactors in some of them. But I need one formula that I can put
all the charactors I want to get rid of and clean up these numbers so I can
work with them the way I need to. Can anyone help me here.

 

[Bob Phillips]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"/",""),"-","")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[beginner here]

Bob even though I didn't start this thread, I have found it very helpful, as
I didn't even know this command existed.

This is one of the few replies that I have tried that actually works.
Example I read somebody's else problem, and I see the propose fix. So I
dupicate(?) what the poster is trying to do, and then apply the fix. Well I
would say about 80% of the time the fix dosen't work.

So wanted to let you know thanks for a reply that does work.

Steve Martin

 

[Bob Phillips]

Thanks, Steve. Glad it helped you as well.

Bob

Bob even though I didn't start this thread, I have found it very helpful, as
I didn't even know this command existed.

This is one of the few replies that I have tried that actually works.
Example I read somebody's else problem, and I see the propose fix. So I
dupicate(?) what the poster is trying to do, and then apply the fix. Well I
would say about 80% of the time the fix dosen't work.

So wanted to let you know thanks for a reply that does work.

Steve Martin

 

[Rick5k via OfficeKB.com]

Bob, that was to simple. I have been trying to combine an IF statement with
the substitute and it just would not work. Thank You. Now if I could get
this to work in access my day would be complete. Any thoughts on this? I
really need to perform this in Access and I just found out this morning that
Substitute is not a built in function. I am going to try it in VBA and se if
that will work. Again, Thanks

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"/",""),"-","")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I have a need to clean/remove dots, dashes, and slashes from a series of part
numbers. The numbers look something like the following: AI246-34-D/34 I

[quoted text clipped - 6 lines]

all the charactors I want to get rid of and clean up these numbers so I can
work with them the way I need to. Can anyone help me here.

 

[Rick5k via OfficeKB.com]

Bob, that was to simple. I have been trying to combine an IF statement with
the substitute and it just would not work. Thank You. Now if I could get
this to work in access my day would be complete. Any thoughts on this? I
really need to perform this in Access and I just found out this morning that
Substitute is not a built in function. I am going to try it in VBA and se if
that will work. Again, Thanks

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"/",""),"-","")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I have a need to clean/remove dots, dashes, and slashes from a series of part
numbers. The numbers look something like the following: AI246-34-D/34 I

[quoted text clipped - 6 lines]

all the charactors I want to get rid of and clean up these numbers so I can
work with them the way I need to. Can anyone help me here.