A question about decoding, substitute or vlookup of character.

  • Thread starter Thread starter Alan Pong
  • Start date Start date
A

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
 
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 >
 
Back
Top