Hi,

I've the following questions : -

A)Is there anyway to remove "( )", "[ ]" or "{ }" from the numbering
in a cell ?, ie the result is only an alphanumeric and will be
I0307981PS,I04L04513,I0220105S for the following examples.

It seems that SUBSTITUTE function fails to solve this question, ie
=SUBSTITUTE(A2,"()","")

E.g.

1) I03-07981(P-S)
2) I04-[L]04513
3) I02-201{05}S

B) How to extract a numbering, only an alphanumeric as I030214PPG
without the quotation marks from I-03-02 14*(P-PG) ? Can SUBSTITUTE
function solve this
question as well ?

Please helps

Thanks
Regards
Lenard

Grüezi ltong

ltong schrieb am 30.09.2004

A)Is there anyway to remove "( )", "[ ]" or "{ }" from the numbering
in a cell ?, ie the result is only an alphanumeric and will be
I0307981PS,I04L04513,I0220105S for the following examples.

It seems that SUBSTITUTE function fails to solve this question, ie
=SUBSTITUTE(A2,"()","")

E.g.

1) I03-07981(P-S)
2) I04-[L]04513
3) I02-201{05}S

Try the following formula (its a bit scary ;-)):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),"[",""),"]",""),"{",""),"}","")

B) How to extract a numbering, only an alphanumeric as I030214PPG
without the quotation marks from I-03-02 14*(P-PG) ? Can SUBSTITUTE
function solve this
question as well ?

Maybe you could use a userdifined function like the following:

Public Function SubstCustom(rngCell As Range, ParamArray characters())
Dim substChar As Variant
SubstCustom = rngCell
For Each substChar In characters()
SubstCustom = Application.WorksheetFunction.Substitute(SubstCust om, _
substChar, "")
Next substChar
End Function

In the worksheet you can do what the before mentioned formula does like
this:

=SubstCustom(A1,"-","(",")","[","]","{","}")

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

Hi Thomas,

Thanks, it works great.....

Regards
Lenard


Thomas Ramel wrote in message ...
Grüezi ltong

ltong schrieb am 30.09.2004

A)Is there anyway to remove "( )", "[ ]" or "{ }" from the numbering
in a cell ?, ie the result is only an alphanumeric and will be
I0307981PS,I04L04513,I0220105S for the following examples.

It seems that SUBSTITUTE function fails to solve this question, ie
=SUBSTITUTE(A2,"()","")

E.g.

1) I03-07981(P-S)
2) I04-[L]04513
3) I02-201{05}S

Try the following formula (its a bit scary ;-)):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),"[",""),"]",""),"{",""),"}","")

B) How to extract a numbering, only an alphanumeric as I030214PPG
without the quotation marks from I-03-02 14*(P-PG) ? Can SUBSTITUTE
function solve this
question as well ?

Maybe you could use a userdifined function like the following:

Public Function SubstCustom(rngCell As Range, ParamArray characters())
Dim substChar As Variant
SubstCustom = rngCell
For Each substChar In characters()
SubstCustom = Application.WorksheetFunction.Substitute(SubstCust om, _
substChar, "")
Next substChar
End Function

In the worksheet you can do what the before mentioned formula does like
this:

=SubstCustom(A1,"-","(",")","[","]","{","}")

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(A1,"(",""
),")",""),"[",""),"]",""),"{",""),"}","")

Regards

Trevor


"ltong" wrote in message
om...
Hi,

I've the following questions : -

A)Is there anyway to remove "( )", "[ ]" or "{ }" from the numbering
in a cell ?, ie the result is only an alphanumeric and will be
I0307981PS,I04L04513,I0220105S for the following examples.

It seems that SUBSTITUTE function fails to solve this question, ie
=SUBSTITUTE(A2,"()","")

E.g.

1) I03-07981(P-S)
2) I04-[L]04513
3) I02-201{05}S

B) How to extract a numbering, only an alphanumeric as I030214PPG
without the quotation marks from I-03-02 14*(P-PG) ? Can SUBSTITUTE
function solve this
question as well ?

Please helps

Thanks
Regards
Lenard