Changing prices to letters

My boss wants to convert our price column to a letter code. as in $4.50 would be DEI
using this template:
S I M P L Y W O R K
1 2 3 4 5 6 7 8 9 0

where each number becomes a number in the words simply work.

Is there a way to create a formula or a macro the when the price is typed in a cell that it changes to the alphs code?

Thanks
Dan

Actually column E will have the prices, and any price entered will need to be changed to te corresponding letter.

Originally Posted by vcsindy

$4.50 would be DEI
using this template:
S I M P L Y W O R K
1 2 3 4 5 6 7 8 9 0

Can you explain why 450 would be "DEI"?
Isn't it "PLK"?

Sorry You're right...PLK. How would I isert this formula

This will covert only numbers to the correspoding letters, ignoring no numeric characters.
i.e.
12xyz45 becomes "IMLY"

Change any cell(s) in Col.E

To a WorkSheet module in question.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, txt As String, x, i As Long
    If Not Intersect(Target, Columns("e")) Is Nothing Then
        Application.EnableEvents = False
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[^\d]+"
            For Each r In Intersect(Target, Columns("e"))
                x = Split(StrConv(.Replace(r.Text, ""), vbUnicode), Chr(0))
                For i = 0 To UBound(x) - 1
                    x(i) = Mid$("SIMPLYWORK", x(i) + 1, 1)
                Next
                r.Value = Join$(x, "")
            Next
        End With
        Application.EnableEvents = True
    End If
End Sub

tHANK yOU SO MUCH....hOW DO i USE IT? dO i PASTE IT TO THE CELL?

Is there anyway the 0 before the decimal can associate with the K so 450.00 will be LYK?

It is not a function used in cell.

Try change any cell in Sheet1 Col.E in attached, you will see how it works.

Thank you thank you thank you!!! You are awesome

OOps

1) Right click on the Sheet1 sheet tab
2) select [View Code]

Then you will see the codes in the right pane.

Replace following one line

                    x(i) = Mid$("SIMPLYWORK", x(i) + 1, 1)

with following 2 lines

                    If x(i) = 0 Then x(i) = 10
                    x(i) = Mid$("SIMPLYWORK", x(i), 1)

Replace the code with the following

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, txt As String, x, i As Long, flg As Boolean, dg As Single
    Const myCode = "SIMPLYWORK"
    If Not Intersect(Target, Columns("e")) Is Nothing Then
        Application.EnableEvents = False
        For Each r In Intersect(Target, Columns("e"))
            If Len(r.Value) Then
                x = Split(StrConv(r.Text, vbUnicode), Chr(0))
                For i = 0 To UBound(x) - 1
                    Select Case True
                        Case x(i) Like "[1-9]"
                            x(i) = Mid$(myCode, x(i), 1)
                            If flg Then x(i) = LCase$(x(i)): dg = dg + 1
                            If dg = 2 Then ReDim Preserve x(i): Exit For
                        Case x(i) = 0
                            x(i) = Mid$(myCode, 10, 1)
                            If flg Then x(i) = LCase$(x(i)): dg = dg + 1
                            If dg = 2 Then ReDim Preserve x(i): Exit For
                        Case x(i) = "."
                            If flg Then
                                If dg = 2 Then
                                    ReDim Preserve x(i - 1)
                                    Exit For
                                Else
                                    x(i) = ""
                                End If
                            End If
                            flg = True
                        Case Else: x(i) = ""
                    End Select
                Next
                r.Value = Join$(x, "") & IIf(Not flg, ".", "") _
                & String(2 - dg, Right$(LCase$(myCode), 1))
                flg = False: dg = 0
            End If
        Next
    End If
    Application.EnableEvents = True
End Sub

@ jindon,

Awesome!!!

Is there a way to convert the number from the left column to the aphabets. Ex cell B1 is 1, it automatically produces the result S in cell C1. (using SIMPLYWORK as the example)