Extract Number without quotation marks

  • Thread starter Thread starter ltong
  • Start date Start date
L

ltong

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
Click to expand...

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

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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 ?
Click to expand...

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(SubstCustom, _
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]
 
One way:

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

Regards

Trevor
 
Hi Thomas,

Thanks, it works great.....

Regards
Lenard


Thomas Ramel said:
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
Click to expand...

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

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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 ?
Click to expand...

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(SubstCustom, _
substChar, "")
Next substChar
End Function

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

=SubstCustom(A1,"-","(",")","[","]","{","}")
Click to expand...
 
Back
Top