Excel 2007 : Converting Letters to Numbers

[Kris_Ngo]

Hi,

I am new here. I am using Microsoft Excel v2004 in Mac OS.. I need to determine the cost of our goods.

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

Could you help me write a very simple code that I could maintain and use for a very long while because I don't think I can manually decode each of the codes since there are more than 5000 of them.

The things to take note of are that you need to omit the first letter and the last number of each code because they are not part of the cost of the item. Then, I need to convert the remaining letters into numbers.

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

P.S. For the code, I could private message or email you what letter corresponds to what number

Hope to hear from you guys soon.


-K

[JBeaucaire]

Since no one here knows (or cares) which company you are with, continue to keep that info to yourself and post the decoding letters here. Or add them on another sheet in your uploaded workbook. You can delete that later if you wish.

Are you looking for a worksheet formula solution? Is VBA OK?

You want to keep these "cost" values secret, so what are you wanting done with these decoded costs strings? Sounds to me like you already have them elsewhere. What's the end goal here?

[Kris_Ngo]

Hi JB,

Those are good questions.

It wouldn't matter to me whether it is a worksheet formula solution or VBA as long as I could easily maintain and use it all the time.

It would be better if I have these cost values a secret.

For the decoded costs strings, I would like them all to be added.

The end goal here is to determine how much we spent for all the goods we gave to a branch for a certain period. It would be convenient if I could use the formula solution or vba again the next time I would do this task for a different time frame.

The secret code we use is PINGSWATCH which translates to 1234567890 respectively.

Thanks for the quick reply.

[Marcol]

Hi Kris_Ngo

Location Secret, Company Secret, Problem Secret, Result Secret?

Suggested Reading

Dan Brown - “The DaVinci Code” or "The Solomon Key"

[DonkeyOte]

Not entirely sure I follow but if the cost for F2 is 966, F3 1242 etc then perhaps:

Please Login or Register  to view this content.

obviously the PINGSWATCH value could be stored in a cell and referred to by reference rather than explicit inclusion in the formula string itself.

[Kris_Ngo]

Hi DonkeyOte,

Yes, that is what I want to happen.

The code you gave works well.

Thanks for the help.

[Kris_Ngo]

Hi DonkeyOte,


As a recap, you have helped me with a code that omits the first letter and the last number of a string of characters, at the same time converts the letters in between into numbers, which are shown on the adjacent column. I have been using your code. It works great, but sometimes it doesn't convert the letters to the right numbers. What could be the problem?

In the file, I have highlighted what should have been the right amount when the letters were converted..

Will look forward to your reply.


Sincerely,

Kristoffer Ngo

[DonkeyOte]

Per your sample I believe C7 is also incorrect and should read 3067 rather than 4067.

The issue is down to H and the fact that the SEARCH returns 10 rather than 0 - when adjusted to power of n this generates the incorrect value.

To correct, apply a MOD to the SEARCH result based on 10, eg:

Please Login or Register  to view this content.

this should adjust the results per requirements.

[Kris_Ngo]

Hey DonkeyOte,

Thanks again for the help. The code is working perfectly now.