Convert a Number Code to a Text Code

T

Traye

I am in the wholesale business and I am wanting to take a number code and
convert it to a text code. For example
$3.50 would equal GTD
Is there anyone who could help me with this issue. Thank You
 
P

Peo Sjoblom

You can make a one time 2 columns table with the dollar values in
the left column and their text codes adjacent in the right column, you can
put it in a hidden sheet then just use something like


=IF(A2="","",VLOOKUP(A2,hidden_Sheet!A1:B50,2,0))

If you really mean converting you need to post more info since it is unclear
what logic you are using
 
T

Traye

Okay I'm using a code of
L I G H T B R E A D
1 2 3 4 5 6 7 8 9 0

So when I enter in for example 2.34 the code IGH would pop up
 
D

Dave Peterson

Could you use a VBA procedure?

If yes, then you could create a user defined function that would translate your
number into a string (and a string back into a number).

If you want to try:

Option Explicit
Function myTranslate(Str As Variant, _
Optional ShowNumbers As Boolean = False) As Variant

Dim iCtr As Long
Dim cCtr As Long
Dim myKey As String
Dim myTrans As String
Dim FoundIt As Boolean
Dim myChar As String

'L I G H T B R E A D
'1 2 3 4 5 6 7 8 9 0
myKey = LCase("lightbread")

If Len(myKey) <> 10 Then
myTranslate = "Error in key"
Exit Function
End If

If ShowNumbers Then
Str = LCase(Str)
myTrans = ""
For iCtr = 1 To Len(Str)
For cCtr = 1 To 10
FoundIt = False
If Mid(Str, iCtr, 1) = Mid(myKey, cCtr, 1) Then
myTrans = myTrans & (cCtr Mod 10)
FoundIt = True
Exit For
End If
Next cCtr
If FoundIt = False Then
myTranslate = "Error in String"
Exit Function
End If
Next iCtr
myTranslate = myTrans / 100
Else
myTrans = ""
If IsNumeric(Str) = False Then
myTranslate = "Not a Number!"
Exit Function
End If
Str = Format(Str, "0.00")
For iCtr = 1 To Len(Str)
myChar = Mid(Str, iCtr, 1)
If myChar = "." Then
'skip it
Else
If myChar = "0" Then
myChar = "10"
End If
myTrans = myTrans & Mid(myKey, myChar, 1)
End If
Next iCtr
myTranslate = UCase(myTrans)
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Put 3.50 in a A1
Put this in B1:
=myTranslate(A1)

And put this in C1:
=mytranslate(b1,true)

You should see GTD in B1 and 3.5 (format the cell the way you want) in C1.
 
Top