A question about decoding, substitute or vlookup of character.

[Alan Pong]

excel 97

sheet1:
y@o
l$ng
zh#ng
l!

sheet2:
! a
@ i
# o
$ u

decoding sheet1, i want the output to sheet3 be:
yio
lung
zhong
la

is there any easy worksheetfunction to do so?
thanks.
rgds.
alan
--END

 

[keepitcool]

You could do it with following formula.

The first column of list on Sheet2 is NAMED decodeFM
The second column of list on Sheet2 is NAMED decodeTO

Then this formula would translate it.

Note that there can be only 1 coded character in the name,
(but it can have multiple occurances within that name)

The formula is an ARRAY formula:
it MUST be entered with CTRL SHFT ENTER:

=SUBSTITUTE(A2,INDEX(decodeFM,MATCH(1,N(A2<>SUBSTITUTE
(A2,decodeFM,decodeTO)),0)),INDEX(decodeTO,MATCH(1,N(A2<>SUBSTITUTE
(A2,decodeFM,decodeTO)),0)))

if it doens't work => try reentering it as an ARAAY,
on the formula bar you MUST see CURLY BRACES around the formula.
{=SUBST...}



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Alan Pong]

keepitcool, thanks for the answer.
rgds.
alan