LinkBack URL
About LinkBacks
how to convert the letter to number ?
example :
A=1, B=2, C=3, D=4, E=5
When i key in cdba , it will show 3421
thanks in advance.
how to convert the letter to number ?
example :
A=1, B=2, C=3, D=4, E=5
When i key in cdba , it will show 3421
thanks in advance.
Have you tried to "reverse" the suggestion from your other (similar-but-opposite) thread?
http://www.excelforum.com/excel-form...-alphabet.html
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
In A1 Cell
Please Login or Register to view this content.
zqa
In B1 Cell
=GetVal(A1)
Result will be 26171
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
A little bit shorter function
Please Login or Register to view this content.
Hi lancer102rus,
Nice approach
Cells(1, Mid(v.Text, i, 1)).Column
But it will throw some errors when the input data is not a column reference
Like
58a%
The result will be #VALUE!
Hi,
unless a delimiter is used which states that zqa means three columns (couldnīt it mean zq and a as well)? Same is to be mentioned for the output as I would rather prefer to see a relation between input and itīs output (just my bad taste in order to follow what means what).
Ciao,
Holger
Last edited by HaHoBe; 09-10-2014 at 12:56 AM.
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hi Sixthsense!
Of course working with exceptions is taking more time that the code itself. But TS said "how to convert the letter to number" and I took it as an axiom :-)
Hm... Yes I thought about it while writing the code but OP have not used any delimiter in the example. So I kept it as it is and waiting to get the OP's comment for further developmentsOriginally Posted by HaHoBe
i wrote like this
Function GetVal(v As Variant) As String
Dim iChr As Integer, sRes As String
If TypeName(v) = "Range" Then v = v.Cells(1).Value
For a = 1 To Len(v)
iChr = Asc(UCase(Mid(v, i, 1)))
If iChr > 64 And iChr < 91 Then sRes = sRes & (iChr - 64)
Next a
For b = 1 To Len(v)
iChr = Asc(UCase(Mid(v, i, 1)))
If iChr > 64 And iChr < 91 Then sRes = sRes & (iChr - 64)
Next b
GetVal = sRes
End Function
it is wrong ?
What you are trying to do with a b loop?
Just wanted to bring it to your notice so that you may use error handlers to avoid such data's
I want my key in have any letter it will be convert to number. sometime the letter have not i set it will be not convert it.
example :
B=1 , C=2 , D=3 , F=4 , G=5
BCF summer , it will be 124 summer.
Please guide me on the more convert letter in VBA because i have no idea about VBA. if you just give me 1 letter VBA, i will not know how to continue the 2nd letter.
Thanks in advance.
sorry Sixthsense , i should be
For a = 1 To Len(v)
For b = 2 To Len(v)
Currently i need the VBA for above .
example :
B=1 , C=2 , D=3 , F=4 , G=5
BCF summer , it will be 124 summer.
Last edited by choy96; 09-12-2014 at 03:50 AM.
Modified Sixthsense's code. Seems like you only want to convert some letters. You can add in more "conversions" to be done to the v2 array.
Please Login or Register to view this content.
Last edited by millz; 09-12-2014 at 04:24 AM.
多么想要告诉你 我好喜欢你
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks