472,981 Members | 1,536 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,981 developers and data experts.

How to replace number to Alphabet

272 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 10054
SioSio
272 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
272 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
626 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
220 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

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

Similar topics

3
by: - ions | last post by:
Hi, i would like to know how to replace every char in a string with a certin given char using the String.replace(char oldChar,char newChar). I would like to replace all letters with an underscore...
5
by: Stefan Krah | last post by:
Hello, I am currently writing code where it is convenient to convert char to int . The conversion function relies on a character set with contiguous alphabets. int set_mesg(Key *key, char...
12
by: one | last post by:
greetings i am just wondering if some expert here can either show me how to do this or point me to the right direction (url... i want to use c# to generate a list of alphabet e.g A B C ... AA...
2
by: rob merritt | last post by:
Hi I have a simple problem hopefully. how would I replace all no numerics using <string name>.regex if if I have a string like 123456.309.123456 or 123456-307-123456 or
3
by: Raed Sawalha | last post by:
I have the following letters; string letters = "a;b;c....to z"; the I need to replace the incoming string which containing letters above with integer 1 i did following for(int...
4
by: Brian Henry | last post by:
I have phone numbers like this in a data table 123-435-1234 1231231234 432.234.2321 they all have different formatting, what I want to do is get them all formatted like this (123) 123-1234
31
by: Joe Smith | last post by:
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz" "0123456789" " " "!#%^&*()-_" "+=~\|;:\'" "\"{},.<>/\?" "\a\b\f\n\r\t\v\\" Do the above string literals comprise an alphabet for C?...
23
by: Umesh | last post by:
This is a basic thing. Say A=0100 0001 in ASCII which deals with 256 characters(you know better than me!) But we deal with only four characters and 2 bits are enough to encode them. I want to...
1
by: varann | last post by:
Hello, how can I replace numbers from -1 to 15 with string in MSSQL? For e.g.: -1 = TEST1 1 = TEST2 2 = TEST3 ... 12 = TEST123 13 = TEST1234
4
by: hrprabhu | last post by:
Hi Everyone... Attached is a small database with one table with two columns as shown below. Service Service No. 1 F327445 1 421547 1 8014432 2 O22771 2 L185244
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.