Convert a character to its ASCII value

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
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)
 

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
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.:confused: Your original message said this...

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.
 

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
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.
 

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
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.:LOL:
 

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
' 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.
 

Forum statistics

Threads
1,082,155
Messages
5,363,472
Members
400,741
Latest member
MachoDrove

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top