Convert a string of letters to a number

[niki_cboy]

Hi,

I am new here. I am using Microsoft Excel 2010 and I need to determine the cost of our goods for manufacture.

The problem is that the cost is not in numbers or in the same worksheet, but in a combination of letters and numbers in order to shroud the cost from strangers.

I currently have formulated the data to appear in the relevant cell/worksheet.The data is now being imported from the 'order' worksheet to drop into the relevant cell in the 'costing' worksheet. Once the data is in the costing worksheet, how would I then convert the string of letters into a number.

The secret code that may be used is:

A=0
B=1
C=2
D=3
E=4
F=5
G=6
H=7
I=8
J=9
K=1
L=00

Could you help me write a code that I could maintain and use because I don't think I can manually decode each of the codes since there can be several combinations.

I have attached a file so you guys could get an idea.

Hope to hear from you guys soon.


-Nik

[mike7952]

Here's a UDF that you can use in the worksheet like this

Please Login or Register  to view this content.

Or you could if you wanted to use in a macro. Look at the attached WB.

Manufacturing Template DRAFT.zip

[vishnu01445]

what is maximun lenth of the secret code? (ie..2 digits or 3 digits or unspecified)

here is one simple soluation for 2digits lenth...

=VLOOKUP(LEFT(G4,1),D4:E13,2,0)&VLOOKUP(RIGHT(G4,1),D4:E13,2,0)

[mike7952]

Here's a update to the code. Update to convert the value to Double so you can format the cells to Double or Currency, also if it doesn't find the letter in the code it will tell you what the letter is

Please Login or Register  to view this content.