470,814 Members | 775 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to replace number to Alphabet

270 256MB
VBA code that replaces any numeric value with alphabet (Excel column).

Expand|Select|Wrap|Line Numbers
  1. Function Convert_Number_to_Aplha(ByVal num As Long) As String
  2.     Dim buf As String
  3.     buf = Cells(1, num).Address(True, False)
  4.     Convert_Number_to_Aplha = Left(buf, InStr(buf, "$") - 1)
  5. End Function
Example
Expand|Select|Wrap|Line Numbers
  1. Sub sample1()
  2.     Dim S As String
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Convert_Number_to_Aplha(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8. End Sub
Feb 21 '20 #1
7 9504
SioSio
270 256MB
This code determines if the argument is a number or an alphabet, returns the alphabet if it is a number, and returns the number if it is an alphabet.

Expand|Select|Wrap|Line Numbers
  1. Function Replace_NA_or_AN(ByVal val As Variant) As Variant
  2.     Dim buf As String
  3.     If IsNumeric(val) = True Then
  4.         buf = Cells(1, val).Address(True, False)
  5.         Replace_NA_or_AN = Left(buf, InStr(buf, "$") - 1)
  6.     Else
  7.         Replace_NA_or_AN = Range(val & "1").Column
  8.     End If
  9. End Function
Example

Expand|Select|Wrap|Line Numbers
  1. Sub sample2()
  2.     Dim S As Variant
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Replace_NA_or_AN(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8.     For i = 1 To 40
  9.         S = Cells(1, i).Value
  10.         Cells(2, i).Value = Replace_NA_or_AN(S)
  11.     Next i
  12. End Sub
Feb 21 '20 #2
gits
5,390 Expert Mod 4TB
well - may i ask what realistic usecase could be behind that? So far i never encountered something where i would have had the need for such code in a real scenario?
Feb 21 '20 #3
SioSio
270 256MB
Hi gits
In cell operations (make the cell active, assignment and reference),
I think that it is useful when it can be handled by renge () (multiple) or Cells () (single).
Feb 21 '20 #4
gits
5,390 Expert Mod 4TB
ok thanks - i can see how it might help in demonstrating how to access/modify cells. i was just a bit confused why i would like to replace a number with a character or vice versa in reality. Just never had such a requirement in real life so far - so i just did ask to try to get a bit more context around the plain posted code.
Feb 21 '20 #5
dev7060
575 Expert 512MB
I believe writing bit of theory/explanation related to the code can make it work better for the "articles/how to guides" category.
Feb 26 '20 #6
DaveBonallack
2 2Bits
Hi SioSio,
Do you mean putting numbers into words, like 1234 returns One Thousand Two hundred Thirty-Four?

If so, then this monster formula I found online (apologies to the originator - I've lost your name) works well. No VBA.
Paste it into Cell C3. It converts the digits you've entered in Cell B3 into words

Regards - Dave.

Expand|Select|Wrap|Line Numbers
  1. =IF(OR(LEN(FLOOR(B3,1))>=13,FLOOR(B3,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(B3>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B3),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(B3),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),""))),"  "," ")&IF(FLOOR(B3,1)>1," dollars"," dollar")))
Mar 31 '21 #7
SwissProgrammer
212 128KB
Thank you SioSio.

gits: "what realistic usecase could be behind that?"

In the past, I would send in (sometimes a few minutes apart) detailed construction progress reports, and customer interaction reports, into the main office. Sometimes I thought about the possibility of some other company's personnel reading my reports. At that time, I was careful what I reported, but I used Excel a lot and sometimes considered how to encrypt those reports. Construction competition has occasionally been extremely aggressive. Millions and Billions of dollars have occasionally been at issue. I expect that there have been lots of programmers and lots of penetration attempts of various types since then. UEFI and telemetry and SSL and other such can be penetrated easily. The old use of a "Man in The Middle" software package is no longer needed so much since the average un-knowing or un-caring has given in to allowing UEFI and telemetry or similar on their system.

Before UEFI and Microsoft's so-called "telemetry", I could almost send in Excel based reports without these competition concerns. Almost, I still did filter what I reported, then only talked about some things later in direct person-to-person, but I could send most of it. Now with UEFI, and telemetry, and etc. some things might be better to be encrypted to dissuade other business competitors from intercepting and knowing what the reports said.

Example: if a business customer handed me a signed conditional offer for the company that said that if we finished a certain project a month early, then they guaranteed in writing that they would give us 5 more projects at "Cost plus a Fixed Fee." "Cost Plus a Fixed Fee" agreements are legal and even a preferred federal government contract [X] platform. Wow! Incentive for the company to add more workers and deliver more materials faster, while at the same time some might see that as incentive for a competitor to slow the work down. If a competitor knew of this and paid a sub-contractor to delay their work, then that would not be so nice. This is the real world. Some people see that it is a good idea to be careful and alert.

SSL and such can be broken easily. How to send an encrypted Excel report might have been a concern. Your article has supplied another solution to that. Thank you SioSio.

To use this for Excel encrypted communications: Write an Excel page un-encrypted. Copy that page to another page and then run similar-to-this (as you showed us) on that new page. Send that new page, missing the decryption process to the office. The office personnel imports the received Excel report page into their Excel on their computer and that decrypt's the incoming report via a One Time Pad (unbreakable cipher) [X] [X] chosen from a list of OTPs and sees the original intended report. Entire sentences or other information, pre-defined per referable number or string could be sent.

Once an encryption process like this has been set up, it could be automatic. Some preparation and then another example of applying the Bible verse of "strong fences make good neighbors".

Thank you SioSio.
Mar 31 '21 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by - ions | last post: by
5 posts views Thread by Stefan Krah | last post: by
2 posts views Thread by rob merritt | last post: by
3 posts views Thread by Raed Sawalha | last post: by
4 posts views Thread by Brian Henry | last post: by
31 posts views Thread by Joe Smith | last post: by
23 posts views Thread by Umesh | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.