LinkBack URL
About LinkBacks
looking for a formula that can be convert the number to the alpha .
example A=1 , B=2, C=3, D=4, E=5...
when i key in 34.21 , it will show CD.BD
thks in advance
looking for a formula that can be convert the number to the alpha .
example A=1 , B=2, C=3, D=4, E=5...
when i key in 34.21 , it will show CD.BD
thks in advance
Last edited by choy96; 09-13-2014 at 12:54 AM.
There is not a built in formula or function in Excel that does this, but we can create one and add it to your workbook.
How to install the User Defined Function:
Please Login or Register to view this content.
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
If you were to type 34.21 in cell A2, then this formula in B2 would now give you your desired results:
=NumbersToLetters(A2)
Last edited by JBeaucaire; 09-06-2014 at 02:22 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
ALREADY PASTE THE AS ABOVE INTO VBA AND SAVE AS MACRO-ENABLE. results become #NAME? WHY ?
You didn't post a workbook for me to check your work. I'd guess you didn't follow the instructions exactly and posted the code in the wrong kind of module. But I'm only guessing.
EXAMPLE-ENABLE.xlsm
this is the attachment.
You need to follow these steps to make the UDF suggested by JBeaucaire work correctly.
Open your workbook --> Press Alt+F11 to open VBA editor --> On the VBA editor window --> Insert --> Module --> Paste the code given in post#2 in the code window --> Close the VBA editor --> Save your workbook as Excel Macro-Enabled Workbook --> Done.
Now you can use it like this.....
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Done ... Thks. My problem is although i have save as macro enable , it aslo need to press on menu "yes" to enable macro.
how to add the . to convert it become X ?
example example A=1 , B=2, C=3, D=4, E=5...
when i key in 34.21 , it will show CDXBD
Try this.....
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks