Change 3 letter text string to a number string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete - 31 Dec 2007 19:27 GMT

Hello,

I have a spreadsheet with a string of 3 letters (ATL) per cell, which
represent an airport. There are 24 different 3 letter combinations. I want
to change the 3 letter combination to a corresponding 3 number combination.

Example:
ATL = 300
BDL = 245

Is there a way to do this without modifying auto correct?

Thanks,

Pete

Reply to this Message

Mike H - 31 Dec 2007 19:35 GMT

One way.

Create a tanle of airports and numbers similar to this
Col a Col B
ATL    1
BOS    2
OHR    3
CDG    4
GAT    5

Then if you airpoty code is in C1 theis formula returns its number

=VLOOKUP(C1,A1:B5,2,FALSE)

Mike

> Hello,
>
[quoted text clipped - 11 lines]
>
> Pete

Reply to this Message

Pete - 31 Dec 2007 19:47 GMT

Thanks Mike. I don't know why I didn't think of it this way.

> One way.
>
[quoted text clipped - 27 lines]
> >
> > Pete

Reply to this Message

CLR - 31 Dec 2007 19:36 GMT

This sort of thing is usually done with the VLOOKUP, utilizing a separate
cell. If you are wanting to type in the "ATL" and have Excel automatically
change it to the "300", in the same cell, then that can be done with
macros.......

Vaya con Dios,
Chuck, CABGx3

> Hello,
>
[quoted text clipped - 11 lines]
>
> Pete

Reply to this Message