Data: Characters assigned by numbers (e.g. MARKETINGS assigned as M=0, A=1, R=2 & so up to 9).

Input: Characters

Condition: Apply to Input Characters

Output Should be: Some of the number for the input characters.

Post: Lacks any request for help.

My dear Friends, I apologize for my mistake, I have a request for the program in Excel as below details, can any one help me?

Data: Characters assigned by numbers (e.g. MARKETINGS assigned as M=0, A=1, R=2 & so up to 9).

Input: Characters

Condition: Apply to Input Characters

Output Should be: Some of the number for the input characters.Thanks DerbyDad03

With Best Regards

Your post still makes no sense.

What is it you want?Please explain in more detail.

MIKE

I have to agree with Mike. It's not very clear what you are asking for. When you say

Output Should be:do you meanSomeof the number for the input charactersSUM?As in MARK would equal 6?

TRS would equal 15?

Dear DerbyDad03 & Mike, The data is like, every character of the word MARKETINGS is given the numbers from 0 to 9. (i.e. in sheet 1) Now, when we register or enter characters (e.g. KITE) in one cell of sheet 2, the result in numerical value (i.e. 3654) should be display in other cell of same sheet (i.e. Sheet 2)

Kindly help & advice me if you want any further details, this program I am preparing is for my shops product price code.

Thanks

when we register or enter characters (e.g. KITE)in one cellof sheet 2, the result in numerical value (i.e. 3654) should be display in other cell of same sheet (i.e. Sheet 2)I can not think of any way that this can be done with just a formula, as there is no way of knowing before hand how many letters will be entered into the cell.

Ifthere are only a few letters, then a =VLOOKUP() solution is possible, but once you get beyond a certain limit the formula will exceed allowable limits.This will probably need a VBA solution.

MIKE

You could do it with formula if you are willing to put up with multiple formulas (or one really long Nested IF in 2007). I'm only going to do this for 3 letters. To get every combination for the 10 letters in MARKETINGS, you just need to expand on the concept. Here's the 2003 method...

Put MARKETINGS in A1

Put these in A2:A4:

=FIND(LEFT(B1,1),$A$1)-1

=FIND(LEFT(B1,1),$A$1)-1&FIND(MID(B1,2,1),$A$1)-1

=FIND(LEFT(B1,1),$A$1)-1&FIND(MID(B1,2,1),$A$1)-1&FIND(MID(B1,3,1),$A$1)-1

Put this in C1:

=IF(LEN(B1)=1,A2,IF(LEN(B1)=2,A3,IF(LEN(B1)=3,A4,"etc.")))

As you enter letters in B1, the formula in C1 will determine the length of B1 and grab the corresponding string built by one of the formula in A2:A4.

Edit:

As long as you have enough formulas in A2:A11 for the complete word, this formula can replace the Nested IF offered above:

=INDIRECT("A"&LEN($B$1)+1)

When LEN(B1) = 1, it will return the value in A2, etc.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History