Cost Coding? How in the.....?

[Guest]

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.

 

[Guest]

This may be a double post as I'm not sure if my first replay made it through
or not.

As a test I took some sample data and used the 'Edit'...'Replace' to
replaced the specified number with the proper letter (and removed the .) and
in quite short order I had the proper codes where there used to be currency.

 

[Guest]

Well, while that works ok when you're dealing with a limited amount of data,
I'm looking for a way to automate the process. I would like to be able to
type the cost of an item in the "COST" column and have the appropriate code
automatically be entered into the "COST CODE" column.

 

[Guest]

For a series of legitmate letters in A1
"legitimate" meaning: included in upper case SOUTHPLACE

Try this ARRAY FORMULA*:
B1:
=SUM((MATCH(MID(REPT("E",10-LEN(A1))&A1,{1;2;3;4;5;6;7;8;9;10},1),{"E";"S";"O";"U";"T";"H";"P";"L";"A";"C"},0)-1)*(10^{7;6;5;4;3;2;1;0;-1;-2}))

Note1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note2: In case text wrap impacts the display, there are NO spaces in that
formula.

If A1: SOCCH
B1 returns: 129.95

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

XL2002, WinXP

 

[Guest]

Hmmm....
it appears that you can commit the formula I posted with just [Enter],
instead of [ctrl][shift][enter]. Evidently, it doesn't need to be an array
formula.

(it also works with lower case letters too, but they must be the letters
included in "SOUTHPLACE")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

For a series of legitmate letters in A1
"legitimate" meaning: included in upper case SOUTHPLACE

Try this ARRAY FORMULA*:
B1:
=SUM((MATCH(MID(REPT("E",10-LEN(A1))&A1,{1;2;3;4;5;6;7;8;9;10},1),{"E";"S";"O";"U";"T";"H";"P";"L";"A";"C"},0)-1)*(10^{7;6;5;4;3;2;1;0;-1;-2}))

Note1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note2: In case text wrap impacts the display, there are NO spaces in that
formula.

If A1: SOCCH
B1 returns: 129.95

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

XL2002, WinXP

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.

 

[Guest]

Ooops! I just realized that my post is the exact opposite of what you're
looking for. It converts the text to numbers, instead of vice versa.

My apologies for for wasting anyone's time. : \
***********
Regards,
Ron

XL2002, WinXP

For a series of legitmate letters in A1
"legitimate" meaning: included in upper case SOUTHPLACE

Try this ARRAY FORMULA*:
B1:
=SUM((MATCH(MID(REPT("E",10-LEN(A1))&A1,{1;2;3;4;5;6;7;8;9;10},1),{"E";"S";"O";"U";"T";"H";"P";"L";"A";"C"},0)-1)*(10^{7;6;5;4;3;2;1;0;-1;-2}))

Note1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note2: In case text wrap impacts the display, there are NO spaces in that
formula.

If A1: SOCCH
B1 returns: 129.95

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

XL2002, WinXP

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.

 

[Guest]

I think you need a UDF. I'll write it for you tomorrow, but I would guess
that someone already has one written and will post it before I can get it
done for you.

I'll post back tomorrow.

Jim

 

[Guest]

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)&INDEX(ParseDigits,MIN(5,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(6,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(7,COUNTA(ParseDigits)),1),COUNTA(ParseDigits))

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

 

[Guest]

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

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)&INDEX(ParseDigits,MIN(5,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(6,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(7,COUNTA(ParseDigits)),1),COUNTA(ParseDigits))

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

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.

 

[Guest]

This works, too. It's very similar to yours.

Function test(cost)
Dim Sname() As String, n As Integer, Tcost As String, Pos As String

ReDim Sname(10)
Sname(1) = "S"
Sname(2) = "O"
Sname(3) = "U"
Sname(4) = "T"
Sname(5) = "H"
Sname(6) = "P"
Sname(7) = "L"
Sname(8) = "A"
Sname(9) = "C"
Sname(10) = "E"
Tcost = (cost * 100)
For n = 1 To Len(Tcost)
Pos = Mid(Tcost, n, 1)
If Pos = 0 Then Pos = 10
test = test + Sname(Pos)
Next n
End Function



Regards.
Jim

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

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)&INDEX(ParseDigits,MIN(5,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(6,COUNTA(ParseDigits)),1)&INDEX(ParseDigits,MIN(7,COUNTA(ParseDigits)),1),COUNTA(ParseDigits))

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

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.