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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pokey - 15 Jan 2007 17:41 GMT

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.

Reply to this Message

Earl Kiosterud - 15 Jan 2007 17:56 GMT

=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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>I need help constructing a formula to map a range of numbers to a
> corresponding range of letters like below.
[quoted text clipped - 25 lines]
>
> Any help much appreciated.

Reply to this Message

Pokey - 15 Jan 2007 22:03 GMT

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.

> =CHAR(A1+64)
>
[quoted text clipped - 37 lines]
> >
> > Any help much appreciated.

Reply to this Message

Sandy Mann - 15 Jan 2007 22:36 GMT

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

Signature

HTH

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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Many thanks for the prompt response.
>
[quoted text clipped - 48 lines]
>> >
>> > Any help much appreciated.

Reply to this Message

Earl Kiosterud - 16 Jan 2007 00:18 GMT

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.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>
> Many thanks for the prompt response.
[quoted text clipped - 49 lines]
>> >
>> > Any help much appreciated.

Reply to this Message

Gord Dibben - 16 Jan 2007 01:09 GMT

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

>Pokey,
>
[quoted text clipped - 12 lines]
>where cell is the cell reference of the cell containing the number. THe
>table could be in another sheet, and could be hidden.

Reply to this Message

Pokey - 18 Jan 2007 16:32 GMT

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.

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

Reply to this Message

Roger Govier - 18 Jan 2007 23:25 GMT

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

Signature

Regards

Roger Govier

> All, appreciate the resposnes.
>
[quoted text clipped - 32 lines]
>> >THe
>> >table could be in another sheet, and could be hidden.

Reply to this Message