Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel Programming » Excel Worksheet Functions » [SOLVED] Extracting mid strings from a cell

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 07-04-2009, 06:05 AM
Sultix Sultix is offline
Registered User
  
Join Date: 10 Aug 2007
Posts: 71
Sultix is becoming part of the community
Extracting mid strings from a cell

Please Register to Remove these Ads

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 07:43 AM.
Reply With Quote
  #2  
Old 07-04-2009, 06:34 AM
tigertiger tigertiger is offline
Forum Contributor
  
Join Date: 11 Nov 2008
Location: The Earth
Posts: 191
tigertiger has been very helpful
Re: Extracting mid strings from a cell
This formula for the solution

=SUBSTITUTE(SUBSTITUTE(F3,")",""),"(","")
Last edited by tigertiger; 07-04-2009 at 06:36 AM.
Reply With Quote
  #3  
Old 07-04-2009, 06:46 AM
tigertiger tigertiger is offline
Forum Contributor
  
Join Date: 11 Nov 2008
Location: The Earth
Posts: 191
tigertiger has been very helpful
Re: Extracting mid strings from a cell
sorry, a mistake

Do following the formula
assuming your data at cell F3

=MID(F3,FIND("(",F3)+1,FIND(")",F3)-1-FIND("(",F3))
Reply With Quote
  #4  
Old 07-04-2009, 06:54 AM
Sultix Sultix is offline
Registered User
  
Join Date: 10 Aug 2007
Posts: 71
Sultix is becoming part of the community
Re: Extracting mid strings from a cell
Quote:
Originally Posted by tigertiger View Post
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,

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 06:59 AM.
Reply With Quote
  #5  
Old 07-04-2009, 07:02 AM
tigertiger tigertiger is offline
Forum Contributor
  
Join Date: 11 Nov 2008
Location: The Earth
Posts: 191
tigertiger has been very helpful
Re: Extracting mid strings from a cell
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?
Reply With Quote
  #6  
Old 07-04-2009, 07:03 AM
martindwilson's Avatar
martindwilson martindwilson is online now
Forum Guru
  
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 5,993
martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding
Re: Extracting mid strings from a cell
=substitute(mid(a1,find("(",a1)+1,255),")"
__________________
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code

how to enter array formula

recommended reading
wiki Mojito

how to say no convincingly

most important think you need
Reply With Quote
  #7  
Old 07-04-2009, 07:11 AM
Sultix Sultix is offline
Registered User
  
Join Date: 10 Aug 2007
Posts: 71
Sultix is becoming part of the community
Re: Extracting mid strings from a cell
neither formulas work..

i have the file attached.. please look..

i pasted mine, yours and martins.. none work..
Attached Files
File Type: xls Book2.xls (18.0 KB, 8 views)
Reply With Quote
  #8  
Old 07-04-2009, 07:22 AM
tigertiger tigertiger is offline
Forum Contributor
  
Join Date: 11 Nov 2008
Location: The Earth
Posts: 191
tigertiger has been very helpful
Re: Extracting mid strings from a cell
hmm, that is a formula, you have to type begin =

see cell D2:D6 in attached file, is it result that you hope?
Attached Files
File Type: xls Book2.xls (25.0 KB, 4 views)
Reply With Quote
  #9  
Old 07-04-2009, 07:30 AM
Sultix Sultix is offline
Registered User
  
Join Date: 10 Aug 2007
Posts: 71
Sultix is becoming part of the community
Re: Extracting mid strings from a cell
hello tiger,

i did post without = on purpose

i know why it didnt work previously on F3 Cell .. because i had there " " instead ( ) .. my mistake sorry..

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 ;-)
Reply With Quote
  #10  
Old 07-04-2009, 07:41 AM
tigertiger tigertiger is offline
Forum Contributor
  
Join Date: 11 Nov 2008
Location: The Earth
Posts: 191
tigertiger has been very helpful
Re: Extracting mid strings from a cell
Quote:
Originally Posted by Sultix View Post
hello tiger,
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)),")","")
Reply With Quote
  #11  
Old 07-04-2009, 07:43 AM
Sultix Sultix is offline
Registered User
  
Join Date: 10 Aug 2007
Posts: 71
Sultix is becoming part of the community
Re: Extracting mid strings from a cell
yes both em works.. thank you very much
Reply With Quote
  #12  
Old 07-04-2009, 10:53 AM
martindwilson's Avatar
martindwilson martindwilson is online now
Forum Guru
  
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 5,993
martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding
Re: Extracting mid strings from a cell
oops missed a ) on copying doh
__________________
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code

how to enter array formula

recommended reading
wiki Mojito

how to say no convincingly

most important think you need
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top