Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Convert Number To Letter

  1. #1
    Join Date
    13th May 2008
    Posts
    4

    Convert Number To Letter

    I have an excel spreadsheet filled with numbers (i.e. a 10x10 excel sheet grid) and want to convert the numbers to letters. I want to map from 1-26 all the letters of the English alphabet. So 1=A, 2=B, 3=C, ...26=Z. I am a novice with excel and I know how to do some basic SUM formulas, but that is about it. Can someone point me in the right direction on how to convert this sheet or how to create a macro that can convert this sheet for me? I saw other posts that talk about converting letter grades to numbers then taking the average, etc, but I need to do the opposite. Any help? I am attaching a spreadsheet as an example that I am trying to convert from numbers to letters...

    Thanks,
    ---MC
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Convert Number To Letter

    Hi Mike

    Assume the number you want to convert is in A1 then the following formula will convert it to the corresponding letter:

    =CHAR(64+A1)

    Richard

    ADDED BY ADMIN
    VB:
     'Remove for 0=a
    Option Base 1 '1=a
    Private Sub Worksheet_Change(ByVal Target As Range) 
        Dim MyArray 
        Dim lloop As Long 
         
        MyArray = Array("a", "b", "c", "d", "e", "f", "g", _ 
        "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _ 
        "r", "s", "t", "u", "v", "w", "x", "y", "z") 
        With Target(1, 1) 
            If .Column = 3 Then 
                If IsNumeric(.Value) Then 
                    If .Value >= LBound(MyArray) And .Value <= UBound(MyArray) Then 
                        Target(1, 1).NumberFormat = """" & UCase(MyArray(Target(1, 1))) & """" 
                    End If 
                End If 
            End If 
        End With 
    End Sub 
    
    
    Last edited by Dave Hawley; May 14th, 2008 at 18:37.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th May 2008
    Posts
    4

    Re: Convert Number To Letter

    I added a sample excel spreadsheet of what I am trying to convert. The grids could be 7x7, 10x10, 20x20, etc. So the letters could be in any cell and I wanted to know if there was a macro that I could run to change all the numbers to letters. Also the formula you gave me where would I put it? And how is it run? (I am a novice)
    Thanks...

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Convert Number To Letter

    VB:
    Sub NumbersToLetters() 
        Dim cell As Range 
         
        For Each cell In Range("A1").CurrentRegion 
             
            If cell.Value <= 26 Then _ 
            cell.Value = Chr(cell.Value + 64) 
        Next cell 
    End Sub 
    
    
    HTH

    Bob

  5. #5
    Join Date
    13th May 2008
    Posts
    4

    Re: Convert Number To Letter

    Richard,

    When I try that formula you suggest
    =CHAR(64+A1)

    I get the error:
    #NAME?

    When the number in cell A1 = 26 in the "sample_format.xls" that I attached. Any other suggestions? I think we might be getting close, but I am not sure because I need to convert an entire sheet...

    Thanks...
    ---Mike
    Auto Merged Post Until 24 Hrs Passes;

    Bob,

    I created the macro and your program works! I have one other question which I don't know how to add to your macro. Is there a way to have a number that is greater than 26 subtract 26 from the number until it gets in range between 1 and 26? Does that make sense? If I have a number in a cell that is 46, I want to do 46-26=20 then add 64 to it which correlates to the letter 84 = T. Is there a way to do something like that? <=26?

    We are getting very close to solving the problem I have!!!
    Everyone totally rocks on this site that helps out!!!


    Thanks,
    ---Mike
    Auto Merged Post Until 24 Hrs Passes;

    Bob,

    I created the macro and your program works! I have one other question which I don't know how to add to your macro. Is there a way to have a number that is greater than 26 subtract 26 from the number until it gets in range between 1 and 26? Does that make sense? If I have a number in a cell that is 46, I want to do 46-26=20 then add 64 to it which correlates to the letter 84 = T. Is there a way to do something like that? <=26?

    We are getting very close to solving the problem I have!!!
    Everyone totally rocks on this site that helps out!!!


    Thanks,
    ---Mike
    Last edited by mikec78; May 14th, 2008 at 02:00. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert Number To Letter

    Mike

    How does 84 correlate to T?

    I downloaded your file and was looking in to creating a solution, then I saw that.
    Boo!

  7. #7
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Convert Number To Letter

    VB:
    Sub NumbersToLetters() 
        Dim cell As Range 
         
        For Each cell In Range("A1").CurrentRegion 
             
            If cell.Value Mod 26 > 0 And cell.Value Mod 26 < 26 Then _ 
             
            cell.Value = Chr(cell.Value Mod 26 + 64) 
        Else 
             
            cell.Value = "A" 
        End If 
    Next cell 
    End Sub 
    
    

    Auto Merged Post Until 24 Hrs Passes;

    Quote Originally Posted by norie
    Mike

    How does 84 correlate to T?

    I downloaded your file and was looking in to creating a solution, then I saw that.
    ?chr(84)
    Auto Merged Post Until 24 Hrs Passes;

    Last edited by Bob Phillips; May 14th, 2008 at 04:01. Reason: Auto Merged Doublepost
    HTH

    Bob

  8. #8
    Join Date
    13th May 2008
    Posts
    4

    Re: Convert Number To Letter

    I got it from this chart link that I found:
    http://www.techonthenet.com/ascii/chart.php

    I could be wrong though...
    I am going to test out what you posted above...

    Thanks for all this help Bob, I can't thank you enough...
    Now to test out the new macro you wrote...
    Auto Merged Post Until 24 Hrs Passes;

    The new macro you wrote works like a dream!!!

    Thanks for everything. (I wish I was smart enough to know how you did that)

    I can't thank you enough...
    ---Mike
    Last edited by Dave Hawley; May 14th, 2008 at 10:54.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Convert Number To Letter

    Quote Originally Posted by mikec78
    I got it from this link that I found:
    http://www.techonthenet.com/ascii/chart.php

    I could be wrong though...
    Nope you are right, CHR(84) is T. Norie just didn't read what you wrote properly.
    HTH

    Bob

  10. #10
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert Number To Letter

    Bob

    Yes I didn't read the post properly but you've got to admit the whole thing is a bit confusing.
    Boo!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Convert a column number to a column letter
    By bdonkersgoed in forum EXCEL HELP
    Replies: 27
    Last Post: July 31st, 2014, 10:32
  2. Replies: 6
    Last Post: February 17th, 2008, 11:52
  3. Add Letter To Number
    By Givemethemilk in forum EXCEL HELP
    Replies: 6
    Last Post: August 14th, 2007, 20:34
  4. Convert Letter Grades To Numbers `
    By Pyrrhonic in forum EXCEL HELP
    Replies: 3
    Last Post: June 21st, 2007, 15:15
  5. Convert Column number to Column Letter?
    By Localan in forum EXCEL HELP
    Replies: 2
    Last Post: December 14th, 2005, 14:31

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno