HELP: how do I convert a list of state codes to written states? Also..

  • Thread starter Thread starter twominute00
  • Start date Start date
T

twominute00

I need an easy way in Excel 2002 to do the following:


Convert KY ---> Kentucky


and


Convert a custom list say

ABC ------> Alpha Beta Corp



Easily within my spreadsheet. I can't stand doing this manually
anymore.


If you can help please post your response and I'll get notified by
email.


THANK YOU I AM VERY GRATEFUL -j
 
I can help you with the states, here goes

=VLOOKUP(A1,{"AL","Alabama";"AK","Alaska";"AZ","Arizona";"AR","Arkansas";"CA
","California";"CO","Colorado";"CT","Connecticut";"DE","Delaware";"DC","Dist
rict Of
Columbia";"FL","Florida";"GA","Georgia";"HI","Hawaii";"ID","Idaho";"IL","Ill
inois";"IN","Indiana";"IA","Iowa";"KS","Kansas";"KY","Kentucky";"LA","Louisi
ana";"ME","Maine";"MD","Maryland";"MA","Massachusetts";"MI","Michigan";"MN",
"Minnesota";"MS","Mississippi";"MO","Missouri";"MT","Montana";"NE","Nebraska
";"NV","Nevada";"NH","New Hampshire";"NJ","New Jersey";"NM","New
Mexico";"NY","New York";"NC","North Carolina";"ND","North
Dakota";"OH","Ohio";"OK","Oklahoma";"OR","Oregon";"PA","Pennsylvania";"RI","
Rhode Island";"SC","South Carolina";"SD","South
Dakota";"TN","Tennessee";"TX","Texas";"UT","Utah";"VT","Vermont";"VI","Virgi
n Islands";"VA","Virginia";"WA","Washington";"WV","West
Virginia";"WI","Wisconsin";"WY","Wyoming"},2,0)


beware of line wrapping. Where A1 would be the cell where you type in the
abbr.
You can create a similar list for the custom list, put the abbr. in a column
to the left and its full name in the adjacent cells to the right, then just
refer to that list

=VLOOKUP(A1,B2:C100,2,0)

of course you can do the same for the states

=VLOOKUP(A1,B1:C52,2,0)

the above is just the same hard coded. I would probably tuck it away on
another sheet and give it a defined name like States, then us

=VLOOKUP(A,States,2,0)
 
If the KY and ABC etc. are already entered, a VLOOKUP as Greg suggests will do
the trick.

If you want to enter ABC in a cell and have it converted immediately to
Alpha Beta Corp you could set up your Tools>Autocorrect with these
abbreviations listed.

Note: Autocorrect is case-sensitive

Gord Dibben XL2002
 
I'll bet you did that from memory ... didn't you Peo ?<g>
--
Regards,

RD
---------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
---------------------------------------------------------------------
Peo Sjoblom said:
I can help you with the states, here goes

=VLOOKUP(A1,{"AL","Alabama";"AK","Alaska";"AZ","Arizona";"AR","Arkansas";"CA","California";"CO","Colorado";"CT","Connecticut";"DE","Delaware";"DC","Dist
rict Of
Columbia";"FL","Florida";"GA","Georgia";"HI","Hawaii";"ID","Idaho";"IL","Illinois";"IN","Indiana";"IA","Iowa";"KS","Kansas";"KY","Kentucky";"LA","Louisiana";"ME","Maine";"MD","Maryland";"MA","Massachusetts";"MI","Michigan";"MN","Minnesota";"MS","Mississippi";"MO","Missouri";"MT","Montana";"NE","Nebraska
";"NV","Nevada";"NH","New Hampshire";"NJ","New Jersey";"NM","New
Mexico";"NY","New York";"NC","North Carolina";"ND","North
Dakota";"OH","Ohio";"OK","Oklahoma";"OR","Oregon";"PA","Pennsylvania";"RI","
Rhode Island";"SC","South Carolina";"SD","South
Dakota";"TN","Tennessee";"TX","Texas";"UT","Utah";"VT","Vermont";"VI","Virgi
n Islands";"VA","Virginia";"WA","Washington";"WV","West
Virginia";"WI","Wisconsin";"WY","Wyoming"},2,0)


beware of line wrapping. Where A1 would be the cell where you type in the
abbr.
You can create a similar list for the custom list, put the abbr. in a column
to the left and its full name in the adjacent cells to the right, then just
refer to that list

=VLOOKUP(A1,B2:C100,2,0)

of course you can do the same for the states

=VLOOKUP(A1,B1:C52,2,0)

the above is just the same hard coded. I would probably tuck it away on
another sheet and give it a defined name like States, then us

=VLOOKUP(A,States,2,0)
Click to expand...
 
Yep<g>

--

Regards,

Peo Sjoblom

Ragdyer said:
I'll bet you did that from memory ... didn't you Peo ?<g>
--
Regards,

RD
---------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
---------------------------------------------------------------------
Peo Sjoblom said:
I can help you with the states, here goes
Click to expand...
=VLOOKUP(A1,{"AL","Alabama";"AK","Alaska";"AZ","Arizona";"AR","Arkansas";"CA","California";"CO","Colorado";"CT","Connecticut";"DE","Delaware";"DC","Dist
rict Of
Click to expand...
Columbia";"FL","Florida";"GA","Georgia";"HI","Hawaii";"ID","Idaho";"IL","Illinois";"IN","Indiana";"IA","Iowa";"KS","Kansas";"KY","Kentucky";"LA","Louisiana";"ME","Maine";"MD","Maryland";"MA","Massachusetts";"MI","Michigan";"MN","Minnesota";"MS","Mississippi";"MO","Missouri";"MT","Montana";"NE","Nebraska
";"NV","Nevada";"NH","New Hampshire";"NJ","New Jersey";"NM","New
Mexico";"NY","New York";"NC","North Carolina";"ND","North
Click to expand...
Dakota";"OH","Ohio";"OK","Oklahoma";"OR","Oregon";"PA","Pennsylvania";"RI","
Rhode Island";"SC","South Carolina";"SD","South
Click to expand...
Dakota";"TN","Tennessee";"TX","Texas";"UT","Utah";"VT","Vermont";"VI","Virgi
Click to expand...
 
In order to spare people a lot of work to convert,
copy/paste peo's to a worksheet and use this.

Sub FixLongFormulas()'select 1st line of paste
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
mstr = mstr & C
Next
Cells(x - 1, y) = mstr
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Peo Sjoblom said:
I can help you with the states, here goes

=VLOOKUP(A1,{"AL","Alabama";"AK","Alaska";"AZ","Arizona";"AR","Arkansas";"CA","California";"CO","Colorado";"CT","Connecticut";"DE","Delaware";"DC","Dist
rict Of
Columbia";"FL","Florida";"GA","Georgia";"HI","Hawaii";"ID","Idaho";"IL","Illinois";"IN","Indiana";"IA","Iowa";"KS","Kansas";"KY","Kentucky";"LA","Louisiana";"ME","Maine";"MD","Maryland";"MA","Massachusetts";"MI","Michigan";"MN","Minnesota";"MS","Mississippi";"MO","Missouri";"MT","Montana";"NE","Nebraska
";"NV","Nevada";"NH","New Hampshire";"NJ","New Jersey";"NM","New
Mexico";"NY","New York";"NC","North Carolina";"ND","North
Dakota";"OH","Ohio";"OK","Oklahoma";"OR","Oregon";"PA","Pennsylvania";"RI","
Rhode Island";"SC","South Carolina";"SD","South
Dakota";"TN","Tennessee";"TX","Texas";"UT","Utah";"VT","Vermont";"VI","Virgi
n Islands";"VA","Virginia";"WA","Washington";"WV","West
Virginia";"WI","Wisconsin";"WY","Wyoming"},2,0)


beware of line wrapping. Where A1 would be the cell where you type in the
abbr.
You can create a similar list for the custom list, put the abbr. in a column
to the left and its full name in the adjacent cells to the right, then just
refer to that list

=VLOOKUP(A1,B2:C100,2,0)

of course you can do the same for the states

=VLOOKUP(A1,B1:C52,2,0)

the above is just the same hard coded. I would probably tuck it away on
another sheet and give it a defined name like States, then us

=VLOOKUP(A,States,2,0)
Click to expand...
 
Back
Top