Extract Number without quotation marks
 

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

Extract Number without quotation marks
 

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]

Extract Number without quotation marks
 

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

Extract Number without quotation marks
 

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,"-","(",")","[","]","{","}")