map a range of numbers to a range of corresponding letters?

  • Thread starter Thread starter Pokey
  • Start date Start date
P

Pokey

I need help constructing a formula to map a range of numbers to a
corresponding range of letters like below.

Each number is a unqie cell, and each letter is in a unique cell.

INPUTS

1 2 3 4 5 6
7 8 9 10 11 12

A B C D E F
G H I J K L


Ideally I would like to write a formula that I can copy straight down a
column that would allow me to enter a number in one column and then
give me the corresponding letter to the right.

For example I would enter:

4 and the formula would produce D, and so on.

11 K
8 H


I've been toying with OFFSET, but I would like to try to avoid a series
of nested "IF" statements b/c of the potential size of my ranges, plus
it just doesn't scale well.

Any help much appreciated.
 
=CHAR(A1+64)

This will work beyond L.

=CHOOSE(A1,"A","B","C","D", ... )

Both will do wierd stuff with an empty cell. We can fix that.
 
Many thanks for the prompt response.

This assumes that #1 will always be "A" and so on. Ultimatley my array
will be in excess of 100, so I've built a series of nested IF
statements that do the necessary math for 26 letter alphabet, and then
combined that with an "&" to get 27 to = "AA" and so on. I believe
this will work even though I am not crazy about nested IFs. The nice
thing is that this is all one-time set up. Of course, if for some
reason I decide to change my mapping, then I suppose I could always use
CHOOSE along with a similar "suffix" to the formula as I've done above.
 
This Chip Pearson Function from 2001 will return a Column name from a number
so it will work up to IV:

Function ColumnLetter(ColNum As Integer) As String
ColumnLetter = Left(Cells(1, ColNum).Address(True, False), 1 - (ColNum >
26))
End Function

It will return the letter, or double letters, corresponding to a number, so
if cell C5 has 100 in it then

=columnletter(C5)

Will give you CV


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Pokey,

It'd be much easier if you made a simple table by putting the numbers in a
single column, and the corresponding letters in a column to the right:

1 A
2 B
3 C
4 D etc.

then used something like

=VLOOKUP(cell, A2:B103, 2, FALSE)

where cell is the cell reference of the cell containing the number. THe
table could be in another sheet, and could be hidden.
 
Using Earl's Vlookup suggestion you can enter this in B2 and copy down to get
letters past Z......AA, AB etc to IV

=SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1","")


Gord Dibben MS Excel MVP
 
All, appreciate the resposnes.

Unfortunately, I can't drop the source data in a column like suggested
above or I would have used the VLOOKUP function.
 
Hi

Then to obtain the result for any number entered in a cell, let's assume
you used cell A4, amend Gord's formula to
=SUBSTITUTE(ADDRESS(1,A4,4),"1","")

No Vlookup's involved it just returns the letter corresponding to any
numeric value in cell A4
 
Back
Top