Hello,
i have in a cell a Text like this..
Chicago (TZGUG)
i tried a formula.. like this..
=LEFT(F3;LEN(F3)-1
to remove only ) ..
but how can i remove the ( too ..
and also Chicago
so the result in a cell would be TZGUG
please help, thanks
the rest cells are all like this..
the left strings are always standard ---> (xxxxx)
Last edited by Sultix; 07-04-2009 at 06:43 AM.
This formula for the solution
=SUBSTITUTE(SUBSTITUTE(F3,")",""),"(","")
Last edited by tigertiger; 07-04-2009 at 05:36 AM.
sorry, a mistake
Do following the formula
assuming your data at cell F3
=MID(F3,FIND("(",F3)+1,FIND(")",F3)-1-FIND("(",F3))
Hello tiger,Originally Posted by tigertiger
doesnt work
could the problem be on bold reference?
FIND("(",F3)+1
])-1-FIND("(",F3))
same thing twice?
Last edited by Sultix; 07-04-2009 at 05:59 AM.
no ,
that work for your sample data
=MID(F3,FIND("(",F3)+1,FIND(")",F3)-1-FIND("(",F3))
differ between the red character
if cell F3 contains
Chicago (TZGUG)
==> the cell result is
TZGUG
what about the error you see?
=substitute(mid(a1,find("(",a1)+1,255),")"
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
neither formulas work..
i have the file attached.. please look..
i pasted mine, yours and martins.. none work..
hmm, that is a formula, you have to type begin =
see cell D2:D6 in attached file, is it result that you hope?
hello tiger,
i did post without = on purpose
i know why it didnt work previously on F3 Cell .. because i had there " " instead ( ) ..
thank you.. that is the result i wanted to see..
is this the shortest it can be done? ... would martins formula work too?
alternative ways is always good to know ;-)
for data at cell B2
the martins's formula, I think, should be
=SUBSTITUTE(MID(B2,FIND("(",B2)+1,255),")","")
an alternative is
=SUBSTITUTE(RIGHT(B2,LEN(B2)-FIND("(",B2)),")","")
yes both em works.. thank you very much
oops missed a ) on copying doh
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks