My IF formula seem uncooperative

[deathzero34]

Hello great wizards of Excel. I am writing this because after searching trough Google for hours I got out of option and just decided to ask experts.

My problem:
I'm working with a database of addresses.
Ex: XXXX Financial Drive, West TowerP.O. Box XXX, Mississauga, ON

Now I want to detect the provinces in this case and copy it to an other cell.
So I went with :
=if(Cell="on","Ontario","")

From what I gathered. The above formula should fix my problem but excel doesn't seem to agree and only give me back #nom?

I am Using Excel2007 and win7 (64bit)

 

[duboost]

how is your data setup? Is the province abbreviation in its own cell?

 

[Sandeep Warrier]

Hi & welcome to the Board!

If your data is structured like below, and the province is always at the end, then, if you have a data table like the one shown, you could probably use

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Address</td><td style=";">Province</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Abbr</td><td style=";">Province</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">XXXX Financial Drive, West Tower P.O. Box XXX,  Mississauga, ON</td><td style=";">Ontario</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ON</td><td style=";">Ontario</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">BC</td><td style=";">British Colombia</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AB</td><td style=";">Alberta</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">NS</td><td style=";">Nova Scotia</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">A2," ",REPT(<font color="Teal">" ",50</font>)</font>),50</font>)</font>),$G$2:$H$5,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[deathzero34]

Thank you for all your help.

I just realize, somehow (I have no idea) my formula change language and went to french. Hence why my if was bugging out.

But now I can at least translate them.

Now I have weird male sex symbol. One problem after an other I guess.

Thx again