Create custom output based upon part of a cell's data

Hey there, I have a two part question and was hoping someone smarter than me could give me a hand.

I get a set of reports based upon computer machine names. I'd like to find a macro or script that will populate a column with data if the data in the first column matches certain criteria. The problem is that the variable I want to poll is in the middle of the machine name. It's a site code number that I need to turn into intuitive data. For example:
columnA
ABC%%%0987654
where %%% is 123
columnB should output
Mexico City

If

columnA
ABC%%%0987654
where %%% is 456
columnB should output
Panama

I have about 60 sites in 7 regions and the data will be different every time but the site/regions won't change. The variables for the sites won't change or move places either. It will always be 3 digits after 3 variable letters.

Now, if I can get that working I'd also like a script (or the same script) to move the data to a second worksheet and populate columns by sorting the sites by region
So if Mexico City is in columnB on sheet 1 it should move (move not copy) to sheet 2 columnA and B (region1) where Panama should move to sheet 2 columnC and D (region2). Moving both the machine name and the intuitive output from the first process.

Any help would be appreciated, I've been struggling with this for a week and google has been a little obtuse about the whole thing.

Conversely, if someone has a better way to do this, I'm all ears.

Thanks in advance.

Anyone? I'd love to get some help, even just a point to some resources where I can research the answers.

I guarantee there is a better way to do it than this. I just can't think of it right now. This does work for what you want to accomplish but you are going to have to hand program the other regions using the same format. You might also need to edit the range and search strings. As to your second question about moving data between the sheets, i am not able to understand what you want to do. A example sheet with a before and after would help.

Option Explicit

Sub InStr()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim icell As Range
Dim lastrow As Long

lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
For Each icell In ws1.Range("A1", "A" & lastrow)
    If InStr(icell.Value, "123") Then
        icell.Offset(0, 1).Value = "Mexico City"
    ElseIf InStr(icell.Value, "456") Then
        icell.Offset(0, 1).Value = "Panama"
    End If
Next icell

End Sub