How to replace number to Alphabet

[SioSio]

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

Function Convert_Number_to_Aplha(ByVal num As Long) As String
    Dim buf As String
    buf = Cells(1, num).Address(True, False)
    Convert_Number_to_Aplha = Left(buf, InStr(buf, "$") - 1)
End Function

Example

Sub sample1()
    Dim S As String
    Dim i As Integer
    For i = 1 To 40
        S = Convert_Number_to_Aplha(i)
        Cells(1, i).Value = S
    Next i
End Sub

[SioSio]

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.

Function Replace_NA_or_AN(ByVal val As Variant) As Variant
    Dim buf As String
    If IsNumeric(val) = True Then
        buf = Cells(1, val).Address(True, False)
        Replace_NA_or_AN = Left(buf, InStr(buf, "$") - 1)
    Else
        Replace_NA_or_AN = Range(val & "1").Column
    End If
End Function

Example

Sub sample2()
    Dim S As Variant
    Dim i As Integer
    For i = 1 To 40
        S = Replace_NA_or_AN(i)
        Cells(1, i).Value = S
    Next i
    For i = 1 To 40
        S = Cells(1, i).Value
        Cells(2, i).Value = Replace_NA_or_AN(S)
    Next i
End Sub

[gits]

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?

[SioSio]

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).

[gits]

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.

[dev7060]

I believe writing bit of theory/explanation related to the code can make it work better for the "articles/how to guides" category.

[DaveBonallack]

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.

=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")))

[SwissProgrammer]

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.