I'm guessing you understand the Mid function, but to review... the first
argument is the text you want to parse and the third argument is how many
characters to pull out; now, for the second argument... this is just the
position in the text that you want to start pulling characters from. I may
have made a bad assumption about this part... I assumed your numbers in
Column B would be 1, 2, 3 (sequentially) up to the number of last state (my
code, as currently written, is completely dependent on this); but now, in
looking back at your original post, I see your numbering may not be
sequential (since, for example, 172 is the value for GA). Can you clarify
how your number codes in Column B relate to the state abbreviations?

--
Rick (MVP - Excel)


"McRibIsBack" wrote in message
...
Hi Rick,

I'm trying this and it seems like what i need, but I'm getting a type
mismatch error:

Sub GetStateAbbreviation()
Dim X As Long, LastRow As Long, StatePosition As Long
Const States = "NC SC NJ FL RI TX NH ME GA CT VA CA AZ NV OR DC MD TN MI
NY MA PA MO IN KS NM IA OK AR IL"
Const StartRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For X = StartRow To LastRow
Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1), 2)
Next
End Sub

I'm a rookie, so the only row I don't really understand the logic is
"Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1), 2)".
I
get lost there sorry! :$

"Rick Rothstein" wrote:

Here is a simple macro you can use...

Sub GetStateAbbreviation()
Dim X As Long, LastRow As Long, StatePosition As Long
Const States = "AB CD EF GH IJ KL MN OP QR ST"
Const StartRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For X = StartRow To LastRow
Cells(X, "A").Value = Mid(States, 1 + 3 * (Cells(X, "B").Value - 1),
2)
Next
End Sub

You need to replace the text I assigned to the States constant (the Const
statement) with a space delimited list of your 2-letter state
abbreviations
(I would have done this, but I don't know the order you have set for the
states). I also limited the States text string to 10 states just for
testing
purposes... you would, of course, put all 50 state abbreviation (space
delimited) between the quote marks... just make sure you put them in the
same order as you want the number in Column B to reflect.

--
Rick (MVP - Excel)



"McRibIsBack" wrote in message
...
Hi, I haven't used arrays in a while so forgive me. I'm converting
numeric
values that represent the U.S. states into the text values. If B1 = 1,
then
A1.value = NC, If B1 = 5, then A1.value = SC. I was making a case
structure
for this, but this will take me forever because I need to check every
state
AND I need to check the entire column "B"! I realized an array would
be
better, but I can't remember how to build them correctly. Here is the
case
structure I was making:
Sub EntityCheck()

Select Case Range("B2").Value

Case 1
Range("A2").Value = "NC"

Case 5
Range("A2").Value = "SC"

Case 35
Range("A2").Value = "NJ"

Case 75
Range("A2").Value = "FL"

Case 99
Range("A2").Value = "TX"

Case 172
Range("A2").Value = "GA"

End Select


I appreciate any help you can offer!

.