Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jplazola
 
Posts: n/a
Default How to convert numbers to corresponding letters? Ex: 123 to abc

I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers
to letters is as follows:
1 = A 6 = F
2 = B 7 = G
3 = C 8 = H
4 = D 9 = I
5 = E 0 = J

For example, I'd like to convert a cell that contains '1250' to 'ABEJ'

I've had no success with the HELP feature in Excel.

Thank you for any help you may provide
Jason

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 28 Jun 2005 13:35:02 -0700, "jplazola"
wrote:

I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers
to letters is as follows:
1 = A 6 = F
2 = B 7 = G
3 = C 8 = H
4 = D 9 = I
5 = E 0 = J

For example, I'd like to convert a cell that contains '1250' to 'ABEJ'

I've had no success with the HELP feature in Excel.

Thank you for any help you may provide
Jason


The problem is concatenating the resultant array of letters. One solution to
that problem is to go to http://xcell05.free.fr/english/.

and download and install Longre's free morefunc.xll add-in.

Then use the MCONCAT function in this *array* formula:

=MCONCAT(CHOOSE(1+MID(A1,ROW(INDIRECT(
"1:"&LEN(A1))),1),"J","A","B","C","D","E","F","G", "H","I"))

To enter an array formula, after typing or pasting it in, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.


--ron
  #3   Report Post  
Eddie O
 
Posts: n/a
Default

try playing with the CODE and CHAR functions.

Eddie O

"jplazola" wrote:

I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers
to letters is as follows:
1 = A 6 = F
2 = B 7 = G
3 = C 8 = H
4 = D 9 = I
5 = E 0 = J

For example, I'd like to convert a cell that contains '1250' to 'ABEJ'

I've had no success with the HELP feature in Excel.

Thank you for any help you may provide
Jason

  #4   Report Post  
bj
 
Posts: n/a
Default

fist make a table with your conversion data
then if all of the number cominations are 4 long

=vlookup(value(mid(number,1,1)),
table,2)&vlookup(value(mid(number,2,1)),table,2)&v lookup(value(mid(number,3,1)),table,2)&vlookup(val ue(mid(number,4,1)),table,2)
if you have varying length of number combinations you can make if more
complex by adding if(len() levels
"jplazola" wrote:

I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers
to letters is as follows:
1 = A 6 = F
2 = B 7 = G
3 = C 8 = H
4 = D 9 = I
5 = E 0 = J

For example, I'd like to convert a cell that contains '1250' to 'ABEJ'

I've had no success with the HELP feature in Excel.

Thank you for any help you may provide
Jason

  #5   Report Post  
Fred
 
Posts: n/a
Default

Try this:
=IF(INT(A1/1000)=0,"J",CHAR(INT(A1/1000)+64))&IF(INT(MOD(A1,1000)/100)=0,"J",CHAR(INT(MOD(A1,1000)/100)+64))&IF(INT(MOD(A1,100)/10)=0,"J",CHAR(INT(MOD(A1,100)/10)+64))&IF(MOD(A1,10)=0,"J",CHAR(MOD(A1,10)+64))

Only works for 4 digit numbers

HTH

"jplazola" wrote:

I am trying to find a formula that will convert existing number combinations
into corresponding letter combinations in Excel. The relationship of numbers
to letters is as follows:
1 = A 6 = F
2 = B 7 = G
3 = C 8 = H
4 = D 9 = I
5 = E 0 = J

For example, I'd like to convert a cell that contains '1250' to 'ABEJ'

I've had no success with the HELP feature in Excel.

Thank you for any help you may provide
Jason

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
no row numbers or column letters DISPLAYED RagDyer Excel Discussion (Misc queries) 4 April 24th 23 03:42 AM
How do I sort letters before numbers in Excel? RiverGirl Excel Discussion (Misc queries) 4 May 27th 05 04:09 PM
How do I convert numbers stored as text with spaces to numbers Baffuor Excel Discussion (Misc queries) 1 May 24th 05 07:39 AM
VLOOKUP for a cell with both letters and numbers Sonohal Excel Discussion (Misc queries) 6 April 8th 05 02:13 PM
How to convert Excel imported numbers from text to numbers? Alden Excel Discussion (Misc queries) 9 April 1st 05 09:51 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"