Spell Numbers

M

Marcelo

Guys,

I am looking for a way to separate digits using VBA, for example. if I have
123 in one cell, and I would like to have a function transforming 1 in A, 2
in B, etc, so the function for 123 should return ABC or CBA for 321.

best regards.

Marcelo from Brazil
 
M

Matt Lunn

Just 1, 2 and 3? Are you going to go past 9/I? In VBA, Chr(65) returns A,
Chr(66) returns B, Chr(67) returns C etc.

HTH
 
G

Guest

Hi
With your number (eg 5) in A2, use =CHAR(64+A2) to get the correct letter.
That's easy - for a single number. To get more than one (eg 543) you'd need
to know how many the maximum number of digits is.

Hope this helps.
Andy.
 
M

Marcelo

thanks

for 1,2,3,4,,6,7,8,9 and 0
1 return A
2 return B
....
9 return I
0 return J

so if I have in A1 a number like 4684 I would like on B1 something like DFHD.
I could use a fuction like:

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "A"
Case 2: GetDigit = "B"
Case 3: GetDigit = "C"
Case 4: GetDigit = "D"
Case 5: GetDigit = "E"
Case 6: GetDigit = "F"
Case 7: GetDigit = "G"
Case 8: GetDigit = "H"
Case 9: GetDigit = "I"
Case 0: GetDigit = "J"
Case Else: GetDigit = ""
End Select
End Function

to have the digits convert but I must "separate" each digit of the entire
number and convert it.

regards and thanks for help
Marcelo



"Matt Lunn" escreveu:
 
M

Marcelo

Thanks Andy, but the numbers could have more than 6 digits.

thanks for your response

"Andy" escreveu:
 
A

aidan.heritage

amend your function slightly to loop for the LENgth of the input


Function GetDigit(Digit As String)
Dim CheckDigit As String
Dim looper
For looper = 1 To Len(Digit)
CheckDigit = Mid(Digit, looper, 1)
Select Case Val(CheckDigit)
Case 1: GetDigit = GetDigit & "A"
Case 2: GetDigit = GetDigit & "B"
Case 3: GetDigit = GetDigit & "C"
Case 4: GetDigit = GetDigit & "D"
Case 5: GetDigit = GetDigit & "E"
Case 6: GetDigit = GetDigit & "F"
Case 7: GetDigit = GetDigit & "G"
Case 8: GetDigit = GetDigit & "H"
Case 9: GetDigit = GetDigit & "I"
Case 0: GetDigit = GetDigit & "J"
Case Else: GetDigit = ""
End Select
Next
End Function
 
A

aidan.heritage

I didn't explain, but I left the case else statement alone as I assumed
that this would mean what had been entered was non numeric, so you
wouldn't want it converted - you should probably also put in an error
trap to handle a zero length, as currently that would return 0
 
Top