ExcelBanter - Spell Numbers

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Spell Numbers (https://www.excelbanter.com/excel-worksheet-functions/91871-spell-numbers.html)

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

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

"Marcelo" wrote:

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

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.

"Marcelo" wrote in message
...
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

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:

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

"Marcelo" wrote:

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

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

thanks for your response

"Andy" escreveu:

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.

"Marcelo" wrote in message
...
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

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

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

thanks for your response

"Andy" escreveu:

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.

"Marcelo" wrote in message
...
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

Aidan, thanks a lot, it's exact what I am looking for

Regards from Brazil
Marcelo

" escreveu:

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

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

thanks for your response

"Andy" escreveu:

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.

"Marcelo" wrote in message
...
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

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

Marcelo wrote:
Aidan, thanks a lot, it's exact what I am looking for

Regards from Brazil
Marcelo

" escreveu:

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

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

thanks for your response

"Andy" escreveu:

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.

"Marcelo" wrote in message
...
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