Code

[alliswell]

ROYALKINGS where
R=1,O=2,Y=3,A=4,L=5,K=6,I=7,N=8,G=9,S=0
Now whenever i type any code for example when i put YLS in a1 i should get 350 in b1 coz y=3, L=5, s=0
I dont want macro. Some formula function or trick.

Thanks n regards
Love u all

 

[Fluff]

You could use something like

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"R",3),"O",2),"Y",3),"A",4)

Just add the rest of the substitutes.

Are you still using xl 2007? There are "simpler" ways if you have 365 or 2021

 

[Joe4]

You could use something like

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"R",3),"O",2),"Y",3),"A",4)

Just add the rest of the substitutes.

Are you still using xl 2007? There are "simpler" ways if you have 365 or 2021

Fluff,
I was thinking the same thing, though hoping their might be an easier way than nesting 10 SUBSTITUTE functions!
On top of that, the SUBSTITUTE function is case-sensitive. They mentioned "yls".
If they want it to be case agnostic, they will need to add the UPPER function to each of those nested SUBSTITUTE functions too!
Not difficult, but that is going to be one LONG formula!

 

[offthelip]

try this:

=MOD(FIND(LEFT(A1,1),"ROYALKINGS"),10)&MOD(FIND(MID(A1,2,1),"ROYALKINGS"),10)&MOD(FIND(MID(A1,3,1),"ROYALKINGS"),10)

 

[Joe4]

try this:

=MOD(FIND(LEFT(A1,1),"ROYALKINGS"),10)&MOD(FIND(MID(A1,2,1),"ROYALKINGS"),10)&MOD(FIND(MID(A1,3,1),"ROYALKINGS"),10)

And to make that case agnostic, just convert all instances of "A1" with "UPPER(A1)", i.e.

=MOD(FIND(LEFT(UPPER(A1),1),"ROYALKINGS"),10)&MOD(FIND(MID(UPPER(A1),2,1),"ROYALKINGS"),10)&MOD(FIND(MID(UPPER(A1),3,1),"ROYALKINGS"),10)

EDIT: I didn't analyze this closely and realize that this works for only exactly 3 characters!

 

[Fluff]

@offthelip what if the OP wants to enter 7 characters, or any other number?

 

[Fluff]

And to make that case agnostic, just convert all instances of "A1" with "UPPER(A1)", i.e.

Or just use SEARCH instead.

 

[alliswell]

Thanks everybody. Thanks a lot everybody.
Dear fluff how to in 375 snd 2021 as u said its easier.

 

[Fluff]

You could use

=CONCAT(RIGHT(SEARCH(MID(A1,SEQUENCE(LEN(A1)),1),"royalkings")))

 

[alliswell]

Joe n fluff gr8. Thanks. Offthelip thanks to you too but it works for 3 digits only what if i need 10 digits code. And thanks to rest of all buddies whom i missed to thank. You all did gr8 job. Mrexcel peoples are GENIUS