Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A question about decoding, substitute or vlookup of character.
keepitcool, thanks for the answer.
rgds. alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
would like to substitute and return value of 0 for vlookup #N/A er | Excel Worksheet Functions | |||
Substitute Function Question #2 | Excel Discussion (Misc queries) | |||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE | Excel Discussion (Misc queries) | |||
Decoding an old macro | Excel Discussion (Misc queries) | |||
Is there a VLOOKUP substitute when data is not in ascending order | Excel Worksheet Functions |