IF Statements

D

Dava Sutts

I hope someone can help??

I Column A i have varous names with a pre fix in brackets :-

Whitley Bay (BO)
Monkseaton (G)
Newcastle (D)

Each of the () represents something else ie (BO) = 3 (G) = 900 (D) = 1800

Is there a formula to pick out the () within each cell to produce what it
means in column B?

Any help will be appreciated
 
R

Rita Palazzi

This might work for you:
=if(right(A1,4)="(BO)",3,if(right(A1,3)="(G)",900,
if(right(A1,3)="(D)",1800,0)))
 
D

Dave Peterson

I would create a table in another worksheet (sheet2??)

And put the strings in column A and the values in column B.

BO 3
g 900
d 1800

Then use a formula to extract the stuff between the ()'s.

If the data is in A1:
=VLOOKUP(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),Sheet2!A:B,2,FALSE)

This'll work as long as the ()'s only show up once.

And if you're not familiar with =vlookup(), Debra Dalgleish has nice
instructions at:
http://www.contextures.com/xlFunctions02.html
 
D

Dava Sutts

Thats great, Thanks Guys

Dave Peterson said:
I would create a table in another worksheet (sheet2??)

And put the strings in column A and the values in column B.

BO 3
g 900
d 1800

Then use a formula to extract the stuff between the ()'s.

If the data is in A1:
=VLOOKUP(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),Sheet2!A:B,2,FALSE)

This'll work as long as the ()'s only show up once.

And if you're not familiar with =vlookup(), Debra Dalgleish has nice
instructions at:
http://www.contextures.com/xlFunctions02.html
Click to expand...
 
Top