I tried to come up with a formula for this, but I just could not. I did
write a UDF yesterday for this, but did not post at that time as I wanted to
see if someone would come up with a formula and you did.

I'll post my code below, though I don't think it's great, but it does seem
to work. However, I deliberately made it so that it would return SOC.CH as I
thought it might be useful to see where the decimal went (though it would be
easy enough to comment this out.)

Function CostCode(sInp As String) As String
Dim sText As String, sChar As String, sCode As String
Dim l As Integer, x As Integer
sText = ""
sCode = "SOUTHPLACE"
For x = 1 To Len(sInp)
sChar = Mid(sInp, x, 1)
If IsNumeric(sChar) Then
If CInt(sChar) 0 And CInt(sChar) <= 9 Then
sText = sText & Mid(sCode, CLng(sChar), 1)
ElseIf CInt(sChar) = 0 Then
sText = sText & "E"
Else
sText = sText & sChar
End If
Else
sText = sText & sChar
End If
Next x
costcode = sText
End Function

Formula - =costcode(B7) where B7 contained $129.95 returned SOC.CH

--
Kevin Vaughn


"Ron Coderre" wrote:

Now, I know a UDF is the preferred way to go on this, but this inelegant
formula approach also works:

With
A price in cell A1 that is less than $99,999.99

Select the cell in Row_1 that would contain the price code.
In this sample case I used cell B1.

Create this named formula:
From the Excel main menu:
<insert<name<define
Names in workbook: ParseDigits
Refers to:
=LOOKUP(MID(Sheet1!A1*100,ROW(INDEX(Sheet1!$A:$A,1 ,1):INDEX(Sheet1!$A:$A,LEN(Sheet1!A1*100))),1),{"0 ";"1";"2";"3";"4";"5";"6";"7";"8";"9"},{"E";"S";"O ";"U";"T";"H";"P";"L";"A";"C"})
Clck the [OK] button

Note: Since text wrap will undoubtedly impact the display, there are NO
spaces in that formula.

B1:
=LEFT(INDEX(ParseDigits,MIN(1,COUNTA(ParseDigits)) ,1)&INDEX(ParseDigits,MIN(2,COUNTA(ParseDigits)),1 )&INDEX(ParseDigits,MIN(3,COUNTA(ParseDigits)),1)& INDEX(ParseDigits,MIN(4,COUNTA(ParseDigits)),1)&IN DEX(ParseDigits,MIN(5,COUNTA(ParseDigits)),1)&INDE X(ParseDigits,MIN(6,COUNTA(ParseDigits)),1)&INDEX( ParseDigits,MIN(7,COUNTA(ParseDigits)),1),COUNTA(P arseDigits))

Note: Since text wrap will also impact that display, there are NO spaces in
that formula either.

If A1: 129.95
B1 returns SOCCH

In that approach, the ParseDigits named formula always calculates on the
price located in the cell immediately to the left of the cell containing
ParseDigits. If you started on cell D1, then ParseDigits would operate on the
price located 3 cells to the left (A1).

Perhaps that Is something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Justin Steiner" wrote:

I work for a computer retailer and we track our product costs and markup
using excel. I need to find a way to convert the numeric value of our cost to
text using a cost-code. Our code is SOUTH PLACE, with each letter of the code
having a corresponding value 1-0. For example, something costing $129.95
would be coded as SOCCH.

As it is, I have to type the cost code for hundereds of products manually
nearly twice a month. If anyone could point me in the right direction, I'd
REALLY appreciate it.