Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spell Numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
How to sum top 5 numbers from the column of numbers | Excel Discussion (Misc queries) | |||
Averaging Numbers when 2 numbers in one cell | Excel Worksheet Functions | |||
Validating random numbers | Excel Worksheet Functions | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |