corresponding numbers and letters

[laceyMKTSP]

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

[Jason Morin]

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

[laceyMKTSP]

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

[laceyMKTSP]

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