A question about decoding, substitute or vlookup of character.
 

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

A question about decoding, substitute or vlookup of character.
 

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<SUBSTI TUTE
(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) wrote:

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

A question about decoding, substitute or vlookup of character.
 

keepitcool, thanks for the answer.
rgds.
alan