How to: Replace each character

[jamiguel77]

Hi friends easy question how to:


Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.


sample:


=Myfunction('ABCD', 'ABC', 'YZ')
return YZD


Howto?


thanks

[Ace_XL]

Try

=SUBSTITUTE(A1,B1,C1)

Where
A1 is ABCD
B1 is ABC
C1 is YZ

[jamiguel77]

worked but ... have a dude:

i have these 2 strings:

string1: Æص¶ÀÁÙÈÊÍ
string2: 123A567890

and have this Passsword = ʶÁ (encrypted)

if i search the first element: Ê on string1 i get a index of 9 then the char on string2(9) = 9
if i search the second element: ¶ on string1 i get a index of 4 then the char on string2(4) = A
if i search the THIRD element: Á on string1 i get a index of 6 then the char on string2(6) = 6

then decodified my password: ʶÁ is 9A6

then with SUBSTITUTE function how do this?
is possible?

thanks

[Ace_XL]

If your password (ʶÁ) is always 3 characters use

=MID(B1,FIND(MID(C1,1,1),A1),1)&MID(B1,FIND(MID(C1,2,1),A1),1)&MID(B1,FIND(MID(C1,3,1),A1),1)

where
A1 is 1st string
B1 is 2nd string
C1 is password

[jamiguel77]

hi frien i am onmy smartphone (not tested your formula) and no isnt always 3 chars....


Need program in VBA?


thanks

[JBeaucaire]

Here is a UDF that will decode the password in a cell using a formula in another cell. Install this UDF in a standard code module.

Please Login or Register  to view this content.

Put a password in a cell, for example B2. Then put this formula in C2 to decode it: =DECRYPT(B2)

[JBeaucaire]

BTW, to have that UDF go the opposite way, just swap the two Strings in the UDF.