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.

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

--

HTH

Bob Phillips

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

"Rick5k" <u28335@uwe wrote in message news:684fc6639b122@uwe...
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 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" wrote:

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

--

HTH

Bob Phillips

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

"Rick5k" <u28335@uwe wrote in message news:684fc6639b122@uwe...
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.

Thanks, Steve. Glad it helped you as well.

Bob

"beginner here" wrote in message
...
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" wrote:

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

--

HTH

Bob Phillips

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

"Rick5k" <u28335@uwe wrote in message news:684fc6639b122@uwe...
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, 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

Bob Phillips wrote:
=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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

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

Bob Phillips wrote:
=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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1