corresponding numbers and letters
 

i was told there is a way to make certain letters equal numbers. EX.
BREADNMILK is 1234567890 / B=1 R=2 and so on. This is so each product has a
digit code for the price. EX. EDB = $3.51. I don't know how to do that but my
boss said it's possible. HELP!!

-stuck at work, seattle
--
thanks for your help

You could use a UDF such as:

Function Convert(rCode As Range) As String

Dim i As Long
Dim strLetter As String
Dim strNumber As String
Dim strPrice As String

For i = 1 To Len(rCode)
strLetter = Mid(rCode, i, 1)
Select Case strLetter
Case "B"
strNumber = "1"
Case "R"
strNumber = "2"
Case "E"
strNumber = "3"
Case "A"
strNumber = "4"
Case "D"
strNumber = "5"
Case "N"
strNumber = "6"
Case "M"
strNumber = "7"
Case "I"
strNumber = "8"
Case "L"
strNumber = "9"
Case "K"
strNumber = "0"
End Select
strPrice = strPrice & strNumber
Next i

Convert = Format(strPrice / 100, "$#,##0.00")

End Function

---

Call it with:

=Convert(A1)

HTH
Jason
Atlanta, GA


"laceyMKTSP" wrote:

i was told there is a way to make certain letters equal numbers. EX.
BREADNMILK is 1234567890 / B=1 R=2 and so on. This is so each product has a
digit code for the price. EX. EDB = $3.51. I don't know how to do that but my
boss said it's possible. HELP!!

-stuck at work, seattle
--
thanks for your help

wow, do i feel like an idiot because that didnt make sense at all....maybe
tell my what to highlight, where to put it, and what to input so it comes out
right. i thought i was pretty good at this thing but yeah, u lost me lol
--
thanks for your help


"Jason Morin" wrote:

You could use a UDF such as:

Function Convert(rCode As Range) As String

Dim i As Long
Dim strLetter As String
Dim strNumber As String
Dim strPrice As String

For i = 1 To Len(rCode)
strLetter = Mid(rCode, i, 1)
Select Case strLetter
Case "B"
strNumber = "1"
Case "R"
strNumber = "2"
Case "E"
strNumber = "3"
Case "A"
strNumber = "4"
Case "D"
strNumber = "5"
Case "N"
strNumber = "6"
Case "M"
strNumber = "7"
Case "I"
strNumber = "8"
Case "L"
strNumber = "9"
Case "K"
strNumber = "0"
End Select
strPrice = strPrice & strNumber
Next i

Convert = Format(strPrice / 100, "$#,##0.00")

End Function

---

Call it with:

=Convert(A1)

HTH
Jason
Atlanta, GA


"laceyMKTSP" wrote:

i was told there is a way to make certain letters equal numbers. EX.
BREADNMILK is 1234567890 / B=1 R=2 and so on. This is so each product has a
digit code for the price. EX. EDB = $3.51. I don't know how to do that but my
boss said it's possible. HELP!!

-stuck at work, seattle
--
thanks for your help

thank-you so, so much for that! i just got it done and its perfect....can i
ask how you learn how to do things like that? i mean, i dont want to have to
come on here and ask people to help everytime i need something. id love to
know how to do it on my own....thank-you again though!!!!
--
thanks for your help


"Jason Morin" wrote:

You could use a UDF such as:

Function Convert(rCode As Range) As String

Dim i As Long
Dim strLetter As String
Dim strNumber As String
Dim strPrice As String

For i = 1 To Len(rCode)
strLetter = Mid(rCode, i, 1)
Select Case strLetter
Case "B"
strNumber = "1"
Case "R"
strNumber = "2"
Case "E"
strNumber = "3"
Case "A"
strNumber = "4"
Case "D"
strNumber = "5"
Case "N"
strNumber = "6"
Case "M"
strNumber = "7"
Case "I"
strNumber = "8"
Case "L"
strNumber = "9"
Case "K"
strNumber = "0"
End Select
strPrice = strPrice & strNumber
Next i

Convert = Format(strPrice / 100, "$#,##0.00")

End Function

---

Call it with:

=Convert(A1)

HTH
Jason
Atlanta, GA


"laceyMKTSP" wrote:

i was told there is a way to make certain letters equal numbers. EX.
BREADNMILK is 1234567890 / B=1 R=2 and so on. This is so each product has a
digit code for the price. EX. EDB = $3.51. I don't know how to do that but my
boss said it's possible. HELP!!

-stuck at work, seattle
--
thanks for your help