| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Letters to Numbers?
I'm hoping someone might be able to suggest an easy way for the following.
I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
not sure what you want or where the letters are, but if A or a was in A1:
range("b1").value = Hex(Asc(UCase(Range("A1").Value))) - 40 -- Gary "DS" wrote in message ... I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
Try a function like the following:
Function Test(Letter As String) As Long Test = Asc(UCase(Letter)) - Asc("A") + 1 End Function This assumes that Letter is a single character between A and Z. You might want to put some validation logic in the code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 21 Oct 2008 08:26:01 -0700, DS wrote: I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
i tested mine and it doesn't work for all of your cases. sorry.
-- Gary "DS" wrote in message ... I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
If A1 have the letter, and B1 has the formula =TRYTHIS(A1), then B1 will
have the value you want Hers is the UDF code Function trythis(myletter) trythis = Asc(myletter) - 64 End Function best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "DS" wrote in message ... I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
That's perfect Chip, thanks for such a quick response.
I only need A-O as things stand, so it works like a charm. Many Thanks, DS "Chip Pearson" wrote: Try a function like the following: Function Test(Letter As String) As Long Test = Asc(UCase(Letter)) - Asc("A") + 1 End Function This assumes that Letter is a single character between A and Z. You might want to put some validation logic in the code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 21 Oct 2008 08:26:01 -0700, DS wrote: I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
If a formula is needed (rather than code callable in VBA) just use
=CODE(A1)-64 Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 21 Oct 2008 12:40:44 -0300, "Bernard Liengme" wrote: If A1 have the letter, and B1 has the formula =TRYTHIS(A1), then B1 will have the value you want Hers is the UDF code Function trythis(myletter) trythis = Asc(myletter) - 64 End Function best wishes |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
Some may find this alternative function of some interest (it is also case
insensitive)... Function LetterToNumber(Letter As String) As Long LetterToNumber = Cells(1, Letter).Column End Function -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... Try a function like the following: Function Test(Letter As String) As Long Test = Asc(UCase(Letter)) - Asc("A") + 1 End Function This assumes that Letter is a single character between A and Z. You might want to put some validation logic in the code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 21 Oct 2008 08:26:01 -0700, DS wrote: I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Letters to Numbers?
Actually, this is probably the better way to write my function...
Function LetterToNumber(Letter As String) As Long LetterToNumber = Columns(Letter).Column End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Some may find this alternative function of some interest (it is also case insensitive)... Function LetterToNumber(Letter As String) As Long LetterToNumber = Cells(1, Letter).Column End Function -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... Try a function like the following: Function Test(Letter As String) As Long Test = Asc(UCase(Letter)) - Asc("A") + 1 End Function This assumes that Letter is a single character between A and Z. You might want to put some validation logic in the code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 21 Oct 2008 08:26:01 -0700, DS wrote: I'm hoping someone might be able to suggest an easy way for the following. I receive a variable as a letter - e.g. A, B, C etc. I need to convert this to a number, so A = 1, B = 2 etc. Currently I use a Select Case, but obviously that can get quite long (the letter can be anything from A-O, so there are 15 separate cases to state). Anyone got any ideas? TiA, DS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert letters to numbers in Excel? | Excel Discussion (Misc queries) | |||
| Convert numbers to letters in excel | Excel Worksheet Functions | |||
| convert letters to numbers | Charts and Charting in Excel | |||
| Convert letters into numbers. i.e. ABCD = 52 | Excel Worksheet Functions | |||
| How to convert numbers to corresponding letters? Ex: 123 to abc | Excel Discussion (Misc queries) | |||
Linear Mode
