Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Convert Number To Letter

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    Code:
    '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, 17:37.

    Comment


    • #3
      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...

      Comment


      • #4
        Re: Convert Number To Letter

        Code:
        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

        Comment


        • #5
          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, 01:00. Reason: Auto Merged Doublepost

          Comment


          • #6
            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!

            Comment


            • #7
              Re: Convert Number To Letter

              Code:
              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;

              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, 03:01. Reason: Auto Merged Doublepost
              HTH

              Bob

              Comment


              • #8
                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, 09:54.

                Comment


                • #9
                  Re: Convert Number To Letter

                  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

                  Comment


                  • #10
                    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!

                    Comment


                    • #11
                      Re: Convert Number To Letter

                      I would use a Number Format.

                      Code:
                      '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

                      Comment


                      • #12
                        Re: Convert Number To Letter

                        Code:
                        Dim oneCell As Range
                        If Not (ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlNumbers) Is Nothing) Then
                            For Each oneCell In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
                                oneCell.Value = Chr((oneCell.Value - 1) Mod 26 + 65)
                            Next oneCell
                        End If

                        Comment

                        Trending

                        Collapse

                        There are no results that meet this criteria.

                        Working...
                        X