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

Convert a character to its ASCII value

This is a discussion on Convert a character to its ASCII value within the Excel Questions forums, part of the Question Forums category; I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows: Col B is formatted ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    23

    Default Convert a character to its ASCII value

    I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows:

    Col B is formatted to type=Number
    A1 holds "N"
    B1 holds =ASC(A1) expected but got "N"

    another attempt:
    B1 holds =ASCII(A1) #NAME

    I searched MRExcel and google for "Excel Convert Character to ASCII" and got no answer.

    Anyone know the answer? Thanks.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,363

    Default Re: Convert a character to its ASCII value

    Quote Originally Posted by John Kauffman View Post
    I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows:

    Col B is formatted to type=Number
    A1 holds "N"
    B1 holds =ASC(A1) expected but got "N"
    ASC is the VB function that returns the ASCII code... in an Excel worksheet formula, the function name is CODE, so try this...

    =CODE(A1)

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    23

    Default Re: Convert a character to its ASCII value

    Thanks, Rick. Ah, I'm revealing my background. I tried CODE("char") in VBA but threw error as below

    precursor works:
    MsgBox (Left(ActiveCell.Value, 1))

    compile Error of "Sub Not defined"
    MsgBox (code(Left(ActiveCell.Value, 1)))

    Tried following syntax, but get runtime error of "method not supported"
    MsgBox (Application.WorksheetFunction.code(Left(ActiveCell.Value, 1)))

    Any thoughts on porting your answer to code?
    Thanks.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,363

    Default Re: Convert a character to its ASCII value

    Quote Originally Posted by John Kauffman View Post
    Thanks, Rick. Ah, I'm revealing my background. I tried CODE("char") in VBA but threw error as below

    precursor works:
    MsgBox (Left(ActiveCell.Value, 1))

    compile Error of "Sub Not defined"
    MsgBox (code(Left(ActiveCell.Value, 1)))

    Tried following syntax, but get runtime error of "method not supported"
    MsgBox (Application.WorksheetFunction.code(Left(ActiveCell.Value, 1)))

    Any thoughts on porting your answer to code?
    Thanks.
    I'm confused. Your original message said this...

    Quote Originally Posted by John Kauffman View Post
    I thought (remembered?) it was easy to convert a char to ASCII using ASC() as follows:

    Col B is formatted to type=Number
    A1 holds "N"
    B1 holds =ASC(A1) expected but got "N"
    which meant to me you were looking for an Excel formula solution which would be the =CODE(A1) formula that I posted. But your latest message is talking about VBA code which does not use the CODE function (as that is strictly a worksheet function) but, rather, use the Asc function that you talked about in your original message....

    Code:
    MsgBox Asc(ActiveCell.Value)
    So at this point, I am not sure what your actual question is anymore.

  5. #5
    New Member
    Join Date
    Oct 2011
    Posts
    23

    Post Re: Convert a character to its ASCII value

    Sorry, Rick. Good point. I was trying to make it simpler. Thanks for patience.

    In my first question I thought that the function name in Excel would be same as in VBA. I tried to make the question simpler by asking in the Excel context.

    Here is overall goal: Sheet1 col A has a list of countries. Code should go down column A, Copy each country name and paste in sheet 2 in the Col that corresponds to the country's first letter. Australia and Austria would be in col A, Zambia and Zimbabwe in col Z.

    I was trying to get the ASCII of the first letter of the country name. From that I could subtract 65 and in the paste use that number as the column argument in Cells(row,col). The use of ASC() in code below returns odd numbers for intColumnTarget (like -12). I've tried adding Application. WorksheetFunction before LEFT but no luck.

    Sub ToDo_MoveAndSortToOtherSheet()
    Dim intColumnTarget As Integer
    Do While Not IsEmpty(ActiveCell)
    intColumnTarget = 66 - Asc(Left(ActiveCell.Value, 1))
    MsgBox (66 - Asc(Left(ActiveCell.Value, 1)))
    Selection.Copy
    Sheets("Countries-Sorted").Select
    Cells(1, intColumnTarget).Select
    ActiveSheet.Paste ' todo: change paste to below last
    Sheets("COuntries-Random").Select
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,363

    Default Re: Convert a character to its ASCII value

    Does this code do what you are ultimately trying to do?

    Code:
    Sub MoveCountriesToProperColumn()
      Dim X As Long, DataLastRow As Long, Col As String
      Const DataStartRow As Long = 1
      DataLastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For X = DataStartRow To DataLastRow
        Col = Left(Cells(X, "A").Value, 1)
        Cells(X, "A").Copy Worksheets("Sheet2").Cells(Rows.Count, Col).End(xlUp).Offset(-(Worksheets("Sheet2").Cells(1, Col).Value <> ""))
      Next
    End Sub
    Notice there is no need to find the numerical equivalent of the column letter as the Cells property can use a string argument for the column reference.

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    23

    Default Re: Convert a character to its ASCII value

    Works Perfect, Brilliant.

    Learning that CELL() accepts a literal is a big help.

    Now I have to go back and parse it in my mind so I understand.

    I'd like to send you a few bucks for a beer. Do you have a PayPal acct?

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,363

    Default Re: Convert a character to its ASCII value

    Quote Originally Posted by John Kauffman View Post
    I'd like to send you a few bucks for a beer. Do you have a PayPal acct?
    I'm glad the code worked for you and if you have any follow up questions, simply post them to this thread and I will (eventually) see them. As for sending me money... do not do it... volunteering to answer newsgroup and forum question is a hobby for me, I do not do it for money. Besides, I hate beer.

  9. #9
    New Member
    Join Date
    Oct 2011
    Posts
    23

    Default Re: Convert a character to its ASCII value

    Offer is there, thansk for your effort.
    I have parsed in my mind and all makes sense except one part of spec for target to paste:

    ...
    ' begin at paste
    ' following points to bottom filled cell of correct column to paste
    'in other words if doing Angloa and already there are Australia and Austria then line below would point to teh lowest current "A" = Austria
    Worksheets("Sheet2").Cells(Rows.Count, Col).End(xlUp)

    ' but then following throws me. I would think from point above the offset would be just (1,0) to go to the empty row below the lowest filled cell. But I can't figure out what this offset is doing, especially the <> at the end.
    .Offset(-(Worksheets("Sheet2").Cells(1, Col).Value <> ""))

    Any help greatly appreciated. Thanks.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,363

    Default Re: Convert a character to its ASCII value

    Quote Originally Posted by John Kauffman View Post
    ' but then following throws me. I would think from point above the offset would be just (1,0) to go to the empty row below the lowest filled cell. But I can't figure out what this offset is doing, especially the <> at the end.
    .Offset(-(Worksheets("Sheet2").Cells(1, Col).Value <> ""))
    Yes, you could write the above like this...

    .Offset(1, 0)

    or, more succinctly, like this...

    .Offset(1)

    and it would work fine. However, unless you had a header row, the first time it writes to a column, the Offset of one row would make the first entry in that column start on Row 2, not Row 1. So, I test to see if the first cell in the column is empty or not. If it is empty, the comparison would be False, and the offset would be zero rows meaning the first cell in the column would be written to. On the other hand, if the first cell is not blank, then we are writing to a column with data in it and we must offset the row by one. The comparison would be True and, in VB, a True value evaluates as minus one (-1), so I prefix the logical expression with a minus sign so that we have minus a minus one which evaluates as plus one, thus yielding our offset of plus one.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com