using an alpha code for numeric value

I am trying to set up a formula that allows the user to input cost of an item on a sales order in a company-used alpha code. For example, A=1 B=2 C=3 etc. Because that is too simple a code, the code is based on a word with ten non-repeating letters, such as BEACHFRONT, where B=1 E=2 A=3 C=4, etc. If an item costs $12.50, the code entered would be BEFT. I could have a separate column with the actual cost and hide it but I would like to have to only enter the cost code once, then have a formula that would convert those letters into their respective numeric values where I could then enter other formulas for figuring selling priced based on established margins.

Any help on how to do that?

Be.ft will return 01.59

This version uses Ucase as part of the routine to make sure that a and A are both supported.

Function CoPricing(EntryCode As String)
'
CoStore = "BEACHFRONT"
    
For Count = 1 To Len(EntryCode)
temp = UCase(Mid(EntryCode, Count, 1))
If temp = "." Then OutputCode = OutputCode & ".": GoTo 20
If InStr(CoStore, temp) = 0 Then GoTo 20
OutputCode = OutputCode & InStr(CoStore, temp) - 1

20 Next
CoPricing = OutputCode*1
End Function

Ok. so I don't know near as much about excel as i thought i did... i KNOW there is so much more i could do with excel but apparently, i'm not even a dilettante.

If you are feeling generous enough to assist, there are 2 questions I would pose, after leading with this info:

The REAL word is PATHFINDER (i was trying to be clever and not give it out, thinking i could easily substitute in the correct code/text but because i am so ignorant of what it is you did there, i cannot do it!) however, PATHFINDER is 1-0, so using your code, the correct "word" as 0 - 9 would be "RPATHFINDE."

Therefore

1) would you be willing to revise the above file so that it uses the correct word

and

2) how the crap do i "use" it in a spreadsheet of my own? The Excel class i took some years back had about a 5 minute section on macros, consisting of, "go to cell A, type xxx, type this, type that, ok there you go - you've written a macro!" Basically, I want to be able to type the coded text in a cell where it displays as that text, but then displays its numerical value in another cell with two decimal places. The cost code is always written to include all digits of a price, dollars and cents, and the code never has decimals. For example, PAFR will mean $12.50 or 12.50 (dollar sign not necessary.)

I appreciate your assistance!

Thanks again,

Alicia

i can actually work with that! thanks!

hmmm. is there a way to reverse that? such as, input 12.50 and have it return the code, which would be PAFR?

have you got a screen shot of that? =)

busted!

thanks anyway. you've been very helpful!

alicia
(born in the great state of texas btw...)

i'm not an idiot but this is totally beyond my skill set. i know =, +, -, *, /, sum, and how to use existing formulas. never learned code of any kind, except how to repeat my name on a screen on my brother's trash80 thirty years ago...

i DID figure out how to modify the formula to work with the cells i was using. go me.

how did texas get on without you before you got there??

it doesn't display the R in values that end in zero. For example, PAFR = 12.50 = PAF. thought it might have been in advanced options to display or not display zero, but that wasn't it.

xx.jpg

tada!!!

you are amazing.

if it helps, i work for a mom and pop company who have expanded into 4 retail stores with ALOT of outside sales and most of what we do is custom. and most of what we do is still done the same way it was 20 years ago when the owner was working out of his garage. we "finally" got individual computers in 2009.. yeah. before that, 8 people shared ONE for email, web searches, ordering, etc. i am trying to streamline some of the things we do because we duplicate SO MUCH work. for instance, a sales order is hand written, a purchase order is hand written for the items we don't stock which could be all or some of the sales order, an invoice is hand written for the items on the sales order, a receipt is hand written for money that comes in, none of our inventory is computerized (we conduct an annual inventory which consists of a dozen teams of 2 where one will call off the cost and quantity of an item to the other who hand writes it on a "form" {i use that term loosely} and then one person extends all the calculations on the "form" and totals each page - huge time suck.)

anyway, i am one of about 3 people who have any discernible skills and what little i have is not nearly enough to do what i would like to do...

this however is a small step in making one person's job a little easier and i do appreciate your contribution.

again, my thanks
alicia

apparently, you can do this mess in your sleep... amazing.

a previous poster did something similar as a user defined code also, but again, my skills are such that i don't know how to USE it, which is why your formula was better for me, because i could work with that.

so thanks? but unless it comes with brief instructions...it's just really pretty code. =)

unlike the code mentioned here:

http://www.npr.org/2014/01/14/262503...codes-onscreen

neat article about code that appears in movies..

yup. until i can up my skills.

=) and thanks again.