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

 

[lrobbo314]

How about this?

Function NTL(d As String) As String
Dim SP() As String: SP = Split(d, ".")
Dim tmp As String: tmp = vbNullString

For i = 0 To UBound(SP)
    For l = 0 To Len(SP(i)) - 1
        tmp = tmp & Chr(Mid(SP(i), l + 1, 1) + 64)
    Next l
    SP(i) = tmp
    tmp = vbNullString
Next i

NTL = Join(SP, ".")
End Function

 

[Kiwi den]

Hi Thanks, but how does this code activate to the required cells?

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can consider...

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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Num2Let just like it was a built-in Excel function. For example,

=Num2Let(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[lrobbo314]

Here's a way to do with with Power Query as well. But I've noticed, that 0s get convertd to @s. The solution below converts from @ to Z, not sure how you wanted to deal with the 0s.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Converted = Table.AddColumn(Source, "Converted", each Text.Combine(List.Transform(Text.ToList(Text.From([Numeric])), each if _ = "." then "." else Text.Replace(Character.FromNumber(Number.From(_)+64),"@","Z"))))
in
    Converted

Book1
	C	D
1	Numeric	Converted
2	4.02	D.ZB
3	7.92	G.IB
4	10	AZ
5	4.21	D.BA
6	4.81	D.HA
7	4.26	D.BF
8	9.8	I.H
9	6.74	F.GD
10	2.97	B.IG
11	1.73	A.GC
12	2.09	B.ZI
13	2.91	B.IA
14	8.36	H.CF
15	9.46	I.DF
16	1.96	A.IF
17	7.34	G.CD
18	5.86	E.HF
19	3.79	C.GI
20	8.99	H.II
21	9.8	I.H
22	7.69	G.FI
23	2.64	B.FD
24	3.72	C.GB
Sheet4

 

[Rick Rothstein]

But I've noticed, that 0s get converted to @s.

I missed that in my original code!!! Here is my code modified to handle it...

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

 

[Kiwi den]

Here is another UDF (user defined function) that you can consider...

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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Num2Let just like it was a built-in Excel function. For example,

=Num2Let(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

thanks works a treat

 

[lrobbo314]

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

 

[Rick Rothstein]

thanks works a treat

You quoted the old code that I posted... it does not handle zeros in your numbers correctly... use the modified code I posted in Message #6 instead.

 

[DanteAmor]

With formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"Z"),1,"A"),2,"B"),3,"C"),4,"D"),5,"E"),6,"F"),7,"G"),8,"H"),9,"I")

And UDF without loop:

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