Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | ||||||||||
2 | Value | ANSI | FILTER | FILTERED | FILTERED ANSI | |||||
3 | New York | 78,101,160,119,32,143,89,160,111,114,107 | xKey | New ork | 78,101,119,32,111,114,107 | |||||
4 | New York | 78,101,160,119,32,143,89,160,111,114,107 | 160 | New York | 78,101,160,119,32,143,89,160,111,114,107 | |||||
5 | ||||||||||
6 | xKey | D4: | {={160;143;89}} | |||||||
7 | 160 | |||||||||
8 | 143 | |||||||||
9 | 89 | |||||||||
Master (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3 | ="Ne"&CHAR(160)&"w"&CHAR(32)&CHAR(143)&"Y"&CHAR(160)&"ork" | |
B4 | ="Ne"&CHAR(160)&"w"&CHAR(32)&CHAR(143)&"Y"&CHAR(160)&"ork" | |
D6 | =FORMULATEXT(D4) | |
D4 | {={160;143;89}} | |
E3 | {=TEXTJOIN(,TRUE,IF(COUNTIF(xKey,CODE(MID(B3, ROW(INDIRECT(1&":"& LEN(B3))), 1))),"",MID(B3,ROW(INDIRECT(1&":"&LEN(B3))),1)))} | |
E4 | {=TEXTJOIN(,TRUE,IF(COUNTIF(D3,CODE(MID(B4, ROW(INDIRECT(1&":"& LEN(B4))), 1))),"",MID(B4,ROW(INDIRECT(1&":"&LEN(B4))),1)))} | |
F3 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(E3, ROW(INDIRECT(1&":"&LEN(E3))), 1))),"EMPTY")} | |
F4 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(E4, ROW(INDIRECT(1&":"&LEN(E4))), 1))),"EMPTY")} | |
C3 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$995, LEN(B3))), 1))),"EMPTY")} | |
C4 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(B4, ROW($A$1:INDEX($A$1:$A$995, LEN(B4))), 1))),"EMPTY")} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Master (2)'!xKey | ='Master (2)'!$B$7:$B$9 |
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | |||
1 | ||||||||
2 | Value | ANSI | FILTER | FILTERED | FILTERED ANSI | |||
3 | New York | 78,101,160,119,32,143,89,160,111,114,107 | xKey | New ork | 78,101,119,32,111,114,107 | |||
4 | New York | 78,101,160,119,32,143,89,160,111,114,107 | 160 | New York | 78,101,119,32,143,89,111,114,107 | |||
5 | ||||||||
6 | xKey | D4: | {={160;143;89}} | |||||
7 | 160 | |||||||
8 | 143 | |||||||
9 | 89 | Y | ||||||
Master (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3 | ="Ne"&CHAR(160)&"w"&CHAR(32)&CHAR(143)&"Y"&CHAR(160)&"ork" | |
B4 | ="Ne"&CHAR(160)&"w"&CHAR(32)&CHAR(143)&"Y"&CHAR(160)&"ork" | |
D6 | =FORMULATEXT(D4) | |
D4 | {={160;143;89}} | |
C7 | =CHAR(B7) | |
C8 | =CHAR(B8) | |
C9 | =CHAR(B9) | |
C3 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$995, LEN(B3))), 1))),"EMPTY")} | |
C4 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(B4, ROW($A$1:INDEX($A$1:$A$995, LEN(B4))), 1))),"EMPTY")} | |
E3 | {=TEXTJOIN(,TRUE,IF(COUNTIF(xKey,CODE(MID(B3, ROW(INDIRECT(1&":"& LEN(B3))), 1))),"",MID(B3,ROW(INDIRECT(1&":"&LEN(B3))),1)))} | |
E4 | {=TEXTJOIN(,TRUE,IF(COUNTIF(D4,CODE(MID(B4, ROW(INDIRECT(1&":"& LEN(B4))), 1))),"",MID(B4,ROW(INDIRECT(1&":"&LEN(B4))),1)))} | |
F3 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(E3, ROW(INDIRECT(1&":"&LEN(E3))), 1))),"EMPTY")} | |
F4 | {=IFERROR(TEXTJOIN(",",TRUE,CODE(MID(E4, ROW(INDIRECT(1&":"&LEN(E4))), 1))),"EMPTY")} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Master (2)'!xKey | ='Master (2)'!$B$7:$B$9 |