How can I convert a group of numbers to a group of letters?

C

CarlG

My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
 
C

CarlG

How would that work on a digit-by-digit basis, to convert, for example, "256"
to something like "CFI"? (Assuming that A B C D E F G F I and J stood for
0-9)
 
K

Kevin Vaughn

That would be tough to do using a formula, at least for me. For the given
example, this would work (note it is CFG not CFI)
=CHAR(LEFT(D15,1)+65)&CHAR(MID(D15,2,1)+65)&CHAR(RIGHT(D15,1)+65)
However, since your originaly example was 2 characters and, presumably the
number of characters could vary, this would quickly fail. A short UDF could
work (note, there is no error checking)

Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
Next i
CharDigit = sTemp
End Function

here is some sample output:
0123456789 ABCDEFGHIJ
3210 DCBA
256 CFG
 
C

CarlG

Looks PERFECT! The "UDF", that is. Now, how do I use it. If my "numbers" are
column A and I want the converted "text" in column B, how do I apply the
formula?
Thanks!
 
G

Gord Dibben

Carl

Have you copied the UDF to a General module in your workbook?

If so, just enter in B1 =CharDigit(A1) and double-click on B1 fill handle to
copy down column A

Just a note here: if you have a space in the string, the UDF will error.


Gord Dibben MS Excel MVP
 
C

CarlG

Ah, so THAT's how you do it.

It's absolute magic. Except that I left out most important part: In our case
the "real" code is not as simple as 0-1 = A-J. In reality, 0-X, 1=I, 2=L,
3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P. I guess that's a whole 'nother thing,
huh?

You've gone way beyond the call of duty already, so if this is a biggy, I'd
be grateful even if you just pointed me in the right direction as to how to
figure this out.

Extreme thanks,
Carl Greene
 
K

Kevin Vaughn

Here is a start to your query. I'm sure you can figure out the missing
pieces. Sorry it took so long. I lost this thread (doing a search on
my name on the microsoft site did not show it.)

Function CharDigit(Cell As String) As String
Dim l As Integer, i As Integer
Dim sTemp As String, sChar As String
sTemp = ""
l = Len(Cell)
For i = 1 To l
' sTemp = sTemp & Chr(Mid(Cell, i, 1) + 65)
' 0-X, 1=I, 2=L,
' 3=E, 4=H, 5=S, 6=G, 7=T, 8=B, and 9=P.
sChar = Mid(Cell, i, 1)
Select Case sChar
Case "0"
sTemp = sTemp & "X"
Case "1"
sTemp = sTemp & "I"
Case "2"
sTemp = sTemp & "L"
Case "3"
sTemp = sTemp & "E"
Case "9"
sTemp = sTemp & "P"
Case Else
sTemp = sTemp & "! " & sChar & " is not in list!"
End Select
Next i
CharDigit = sTemp
End Function
 
C

CarlG

Thanks ever so much. You've solved my problem perfectly and elegantly; I
REALLY appreciate your taking the time.

Carl Greene
 
Top