Field lookup and mapping to create URL

Hi,

Pretty familiar with using Excel for the basics, but need some help doing some mapping to create a new field.

What I want to create is a new field at the end of each Excel record containing a URL in the following format: http://www.mywebsite.com/Shared/Region/State/12345-license.pdf

12345 - is a code contained in column B. Just need to know the syntax for referencing it.
State - we want the spelled out name, but we only have the two-character abbreviation in column H. So if column H contains CT, we want to put 'Connecticut' in the URL in the last column.
Region - is based on state. Valid values are Midwest, West, South, Northeast. I need to do a lookup on the state to determine which region to list in the URL.

Any suggestions would be greatly appreciated. Thanks in advance!

Thank you so much for your quick response! I tried this out and it works perfectly. Your solution was very easy to follow and implement.

You rock!