How to match the content within a ()

[yichuansancun]

I have the following table

India (Bangalore)
India (Mumbai)
China (Shanghai)
China (Beijing)
USA (San Francisco)

My lookup value will only be the city, ie the part that is within the (). How can I use match function to return the row number?

For example, I want to know the row number of text string that contain Shanghai. Will that be:

=MATCH(LEFT(C9,SEARCH("(",C9,2)),$Z$2:$Z$2198,0)

?

Thanks,

 

[T. Valko]

I have the following table

India (Bangalore)
India (Mumbai)
China (Shanghai)
China (Beijing)
USA (San Francisco)

My lookup value will only be the city, ie the part that is within the (). How can I use match function to return the row number?

For example, I want to know the row number of text string that contain Shanghai. Will that be:

=MATCH(LEFT(C9,SEARCH("(",C9,2)),$Z$2:$Z$2198,0)

?

Thanks,

Maybe something like this...

AC2 = Shanghai

=MATCH("*"&AC2&"*",Z:Z,0)

 

[VoG]

This will return the city name

Excel Workbook
	A	B
1	India (Bangalore)	Bangalore
2	India (Mumbai)	Mumbai
3	China (Shanghai)	Shanghai
4	China (Beijing)	Beijing
5	USA (San Francisco)	San Francisco
Sheet3

 

[yichuansancun]

Awesome!!!! Thank you!!!!

 

[T. Valko]

Awesome!!!! Thank you!!!!

Can't tell who you're replying to.

If you did want to extract just the city name here's another way:

Book1
	Z	AA
2	India (Bangalore)	Bangalore
3	India (Mumbai)	Mumbai
4	China (Shanghai)	Shanghai
5	China (Beijing)	Beijing
6	USA (San Francisco)	San Francisco
Sheet1

Formula entered in AA2 and copied down:

=SUBSTITUTE(MID(Z2,FIND("(",Z2)+1,50),")","")