decimal number to letters

[Kiwi den]

Hi All,

I Have a cell (A1) that has a number in it (2.43) and I would like to display it as a code ( cost price) for example A.DC,, using 1=A, 2=B, 3=C, 4=D, 5=E, 6=F, 7=G, 8=H, 9=I, 0=Z

Is this possible using a formula or VBA

Thanks in advance

 

[Tom.Jones]

Let's get it as close to 1 line as possible, lol.

Function NTLL(ByVal Num As Variant) As String
For X = 1 To Len(Num)
  If Mid(Num, X, 1) = "." Then NTLL = NTLL & "." Else NTLL = NTLL & Replace(Chr(Mid(Num, X, 1) + 64), "@", "")
Next
End Function

This UDF give wrong answer for 2.06 -->B.F instead B.ZF

 

[DanteAmor]

This UDF give wrong answer for 2.06 -->B.F instead B.ZF

I guess he skipped the Z
If Mid(Num, x, 1) = "." Then NTLL = NTLL & "." Else NTLL = NTLL & Replace(Chr(Mid(Num, x, 1) + 64), "@", "Z")

 

[Kiwi den]

Rick,

formula works perfectly for most of numbers... I notice if a whole number eg; 31.00 is entered the code shows as CZ, is there any way to include the .00, just to maker the code look consistant?

 

[Scott Huish]

If you ever wanted to change your replacement code you could this, the lowest value in the array corresponds to 0 and the highest to 9:

Function PriceCode(r As String) As String
Dim x As Long, ReplacementChars()
ReplacementChars = Array("Z", "A", "B", "C", "D", "E", "F", "G", "H", "I")
PriceCode = r
For x = 0 To 9
    PriceCode = Replace(PriceCode, x, ReplacementChars(x))
Next x
End Function

 

[Scott Huish]

Did you want whole numbers to always end in .00 or .ZZ?

 

[Kiwi den]

Did you want whole numbers to always end in .00 or .ZZ?

whole numbers to end in ZZ, sorry for confusion

 

[DanteAmor]

I notice if a whole number eg; 31.00 is entered the code shows as CZ, is there any way to include the .00, just to maker the code look consistant?

The result of 31.00 should be CA.ZZ

Try:

Function NumAbc(Num As Variant) As String
NumAbc = Evaluate(Replace("=@(@(@(@(@(@(@(@(@(@(text(" & Num & ", ""#.00""),""0"",""Z""),1,""A""),2,""B""),3,""C""),4,""D""),5,""E""),6,""F""),7,""G""),8,""H""),9,""I"")", "@", "SUBSTITUTE"))
End Function

 

[Rick Rothstein]

Rick,

formula works perfectly for most of numbers... I notice if a whole number eg; 31.00 is entered the code shows as CZ, is there any way to include the .00, just to maker the code look consistant?

Assuming you want all of your numbers to have two decimal places (minimum), try this variation on my code...

Function Num2Let(ByVal Num As Variant) As String
  Dim X As Long
  Num2Let = Format(Num, "0.00")
  For X = 1 To Len(Num2Let)
    If Mid(Num2Let, X, 1) <> "." Then Mid(Num2Let, X) = Replace(Chr(64 + Mid(Num2Let, X, 1)), "@", "Z")
  Next
End Function

 

[Scott Huish]

Function PriceCode(r) As String
Dim x As Long, ReplacementChars()
ReplacementChars = Array("Z", "A", "B", "C", "D", "E", "F", "G", "H", "I")
PriceCode = Format(r, "0.00")
For x = 0 To 9
    PriceCode = Replace(PriceCode, x, ReplacementChars(x))
Next x
End Function

 

[DanteAmor]

If you want the formula.
And start with 0 if you only put decimals

Book1
	A	B
1	0.05	Z.ZE
2		Z.ZE
Hoja12

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,"0.00"),"0","Z"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G"),8,"H"),9,"I")
B2	B2	=NumAbc(A1)

Function NumAbc(Num As Variant) As String
NumAbc = Evaluate(Replace("=@(@(@(@(@(@(@(@(@(@(text(" & Num & ", ""0.00""),""0"",""Z""),1,""A""),2,""B""),3,""C""),4,""D""),5,""E""),6,""F""),7,""G""),8,""H""),9,""I"")", "@", "SUBSTITUTE"))
End Function