XREPLACE is a powerful find/replace solution allowing for replacing by any or all of four categories, i.e. value and/or count and/or type and/or length; single or multiple elements within each category; and control over priorities of all categories
I developed XREPLACE in response to feedback I received for having more control over data substitution capabilities available through the "exclude_types/include_types/replace_with" feature of most of my functions as well as some basic native capabilities.
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
XREPLACE takes seven parameters, one required and six optional, as follows:
A) The first parameter, required, specifies the function input and can be a cell, range, or array
B) The second parameter, optional, specifies the value(s) of any data type (i.e. numbers, texts, blanks "", logicals, or errors) intended to be replaced
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that if the double quotes is used as blank, it will apply to all four types of blanks (see the latest update of XTYPE); if you need to replace specific types of blanks, then you can specify them in the fourth parameter (see below at section D)
Note that the entries in this parameter must be unique; entering repeat values will cause the function to crash with an #N/A error
C) The third parameter, optional, specifies the count(s) of values of any data type (numbers, texts, blanks, logicals, or errors) intended to be replaced, such that any value(s) that occur at the specified number(s) of times will be replaced
Note that if more than one count needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that all four types of blanks (see the latest update of XTYPE) are counted together in this parameter and that this total number will be compared to the number(s) entered in this parameter; if you need to replace specific types of blanks, then you can specify them in the fourth parameter (see below at section D)
Note that the entries in this parameter must be unique; entering repeat counts will cause the function to crash with an #N/A error
Note that only natural numbers can be entered in this parameter. Entering any other numbers will cause the function to crash with an #N/A error. This is accomplished by m PLMN function (see code below)
D) The fourth parameter, optional, specifies the type(s) of data intended to be replaced and takes one or more of twenty-six arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
33, for space blank
34, for pasted blank
41, for TRUE
42, for FALSE
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
1619, for #PYTHON! error
Note that if more than one type needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that you cannot specify a general data type(s) such as 16 (i.e. errors) and specific type(s) such as 162 (i.e. #DIV/0! error) simultaneously; doing so will cause the function to crash with an #N/A error
Note that the entries in this parameter must be unique; entering repeat types will cause the function to crash with an #N/A error
Note that entering any number(s) besides those corresponding to data types will cause the function to crash with an #N/A error
E) The fifth parameter, optional, specifies the character length(s) of string value(s) (i.e. texts) intended to be replaced
Note that only text values within the specified reference are considered for this parameter
Note that space blanks will not be considered as text strings for this parameter. To specifically replace space blanks, you need to use data type 33 in the fourth parameter above (see section D)
Note that only natural numbers can be entered in this parameter. Entering any other numbers will cause the function to crash with an #N/A error. This is accomplished by m PLMN function (see code below)
Note that if none of the values, counts, types, and lengths parameters are specified, the function will return the reference itself unmodified as long as the replacements parameter is also unused. Otherwise, the function will crash to a single #N/A (see below at section F)
F) The sixth parameter, optional, specifies the value(s) intended as replacements for the entry/entries within any or all of the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above)
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that the order of entries in this parameter corresponds to the overall order of the entries within the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above)
Note that the number of entry/entries in this parameter must match the overall number of entries within the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above); otherwise, the function will crash with an #N/A error
G) The seventh parameter, optional, specifies the priorities of the four categories of replacements to be performed and takes any or all of four arguments as follows:
1 for value-based replacement
2 for count-based replacement
3 for type-based replacement
4 for length-based replacement
Note that these arguments correspond to the order of the second, third, fourth, and fifth parameters; therefore, leaving this parameter as omitted will imply the default {1,2,3,4} priorities, i.e. the highest priority will be given to value-based replacement, then count-based, then type-based, and then length-based replacements
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that if fewer than four numbers are used for this parameter, the remaining numbers will be assumed internally based on the default order of {1,2,3,4}; for instance, if you enter {3,1}, the function will internally create the {3,1,2,4} order for replacements, and entering {4} will imply {4,1,2,3} order. Thus, this argument can be used fully or partially regardless of which of the four replacement types are used
Note that if more than four numbers are entered, the function will crash with an #N/A error
Note that the entries in this parameter must be unique; entering repeat values will cause the function to crash with an #N/A error
Note that entering any values besides 1, 2, 3, or 4 will cause the function to crash with an #N/A error
Feel free to use the link in my signature to download my template that contains this as well as all my other functions
Replacing by value, count, type, or length are the four most common ways that I have encountered. If you need replacing based on other criteria, feel free to let me know, and I can either add them to XREPLACE or create another function.
XREPLACE
JAY (parameter-processing core for XREPLACE)
JAZ (prioritized-replacing core for XREPLACE)
JAU (by-value replacement core for XREPLACE)
JAV (by-count replacement core for XREPLACE)
JAW (by-type replacement core for XREPLACE)
JAX (by-length replacement core for XREPLACE)
JBA (inserts single values into array brackets "{}")
XERROR.TYPE (posted separately)
XTYPE (posted separately)
AllTypes
IO (shortened form of ISOMITTED)
PLMN (stands for "parameter limit, multiple natural")
(Returns the parameter if it is at least one natural number (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered, i.e. wrong data type)
PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
ST1F0 (Returns the sum of all entries within the reference that can be logically evaluated to 1 (TRUE), while considering all other entries as zeros)
JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
I developed XREPLACE in response to feedback I received for having more control over data substitution capabilities available through the "exclude_types/include_types/replace_with" feature of most of my functions as well as some basic native capabilities.
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
XREPLACE takes seven parameters, one required and six optional, as follows:
A) The first parameter, required, specifies the function input and can be a cell, range, or array
B) The second parameter, optional, specifies the value(s) of any data type (i.e. numbers, texts, blanks "", logicals, or errors) intended to be replaced
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that if the double quotes is used as blank, it will apply to all four types of blanks (see the latest update of XTYPE); if you need to replace specific types of blanks, then you can specify them in the fourth parameter (see below at section D)
Note that the entries in this parameter must be unique; entering repeat values will cause the function to crash with an #N/A error
C) The third parameter, optional, specifies the count(s) of values of any data type (numbers, texts, blanks, logicals, or errors) intended to be replaced, such that any value(s) that occur at the specified number(s) of times will be replaced
Note that if more than one count needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that all four types of blanks (see the latest update of XTYPE) are counted together in this parameter and that this total number will be compared to the number(s) entered in this parameter; if you need to replace specific types of blanks, then you can specify them in the fourth parameter (see below at section D)
Note that the entries in this parameter must be unique; entering repeat counts will cause the function to crash with an #N/A error
Note that only natural numbers can be entered in this parameter. Entering any other numbers will cause the function to crash with an #N/A error. This is accomplished by m PLMN function (see code below)
D) The fourth parameter, optional, specifies the type(s) of data intended to be replaced and takes one or more of twenty-six arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
33, for space blank
34, for pasted blank
41, for TRUE
42, for FALSE
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
1619, for #PYTHON! error
Note that if more than one type needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that you cannot specify a general data type(s) such as 16 (i.e. errors) and specific type(s) such as 162 (i.e. #DIV/0! error) simultaneously; doing so will cause the function to crash with an #N/A error
Note that the entries in this parameter must be unique; entering repeat types will cause the function to crash with an #N/A error
Note that entering any number(s) besides those corresponding to data types will cause the function to crash with an #N/A error
E) The fifth parameter, optional, specifies the character length(s) of string value(s) (i.e. texts) intended to be replaced
Note that only text values within the specified reference are considered for this parameter
Note that space blanks will not be considered as text strings for this parameter. To specifically replace space blanks, you need to use data type 33 in the fourth parameter above (see section D)
Note that only natural numbers can be entered in this parameter. Entering any other numbers will cause the function to crash with an #N/A error. This is accomplished by m PLMN function (see code below)
Note that if none of the values, counts, types, and lengths parameters are specified, the function will return the reference itself unmodified as long as the replacements parameter is also unused. Otherwise, the function will crash to a single #N/A (see below at section F)
F) The sixth parameter, optional, specifies the value(s) intended as replacements for the entry/entries within any or all of the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above)
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that the order of entries in this parameter corresponds to the overall order of the entries within the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above)
Note that the number of entry/entries in this parameter must match the overall number of entries within the four preceding parameters (i.e. values, counts, types, and lengths discussed in sections B, C, D, and E above); otherwise, the function will crash with an #N/A error
G) The seventh parameter, optional, specifies the priorities of the four categories of replacements to be performed and takes any or all of four arguments as follows:
1 for value-based replacement
2 for count-based replacement
3 for type-based replacement
4 for length-based replacement
Note that these arguments correspond to the order of the second, third, fourth, and fifth parameters; therefore, leaving this parameter as omitted will imply the default {1,2,3,4} priorities, i.e. the highest priority will be given to value-based replacement, then count-based, then type-based, and then length-based replacements
Note that if more than one value needs to be entered, they should be included within array brackets "{}" and separated by commas per native syntax
Note that if fewer than four numbers are used for this parameter, the remaining numbers will be assumed internally based on the default order of {1,2,3,4}; for instance, if you enter {3,1}, the function will internally create the {3,1,2,4} order for replacements, and entering {4} will imply {4,1,2,3} order. Thus, this argument can be used fully or partially regardless of which of the four replacement types are used
Note that if more than four numbers are entered, the function will crash with an #N/A error
Note that the entries in this parameter must be unique; entering repeat values will cause the function to crash with an #N/A error
Note that entering any values besides 1, 2, 3, or 4 will cause the function to crash with an #N/A error
Feel free to use the link in my signature to download my template that contains this as well as all my other functions
Replacing by value, count, type, or length are the four most common ways that I have encountered. If you need replacing based on other criteria, feel free to let me know, and I can either add them to XREPLACE or create another function.
XREPLACE
Excel Formula:
=LAMBDA(reference,[values],[counts],[types],[lengths],[replacements],[priorities],
LET(
r,IF(LEN(TRIM(reference))=0,"",reference),
v,values,
c,counts,
t,types,
l,lengths,
s,replacements,
p,priorities,
d,JAY(v,c,t,l,s),
IF(
IFERROR(ROWS(d),1)=1,
IF(ERROR.TYPE(d)=14,IF(IFERROR(COLUMNS(replacements),0)=0,r,NA()),NA()),
IF(
AND(
SUM(
IF(IO(values),0,IFERROR(COLUMNS(v),1)),
IF(IO(counts),0,COLUMNS(c)),
IF(IO(types),0,COLUMNS(t)),
IF(IO(lengths),0,COLUMNS(l))
)=IFERROR(COLUMNS(replacements),1),
IF(
IO(counts),
1,
AND(MAX(FREQUENCY(c,c))=1,NOT(ISERROR(PLMN(c))))
),
IF(
IO(types),
1,
AND(MAX(FREQUENCY(t,t))=1,NOT(ISERROR(MATCH(t,AllTypes,0))))
),
IF(
IO(lengths),
1,
AND(MAX(FREQUENCY(l,l))=1,NOT(ISERROR(PLMN(l))))
),
IF(
IO(priorities),
1,
AND(NOT(ISERROR(MATCH(p,{1,2,3,4},0))),MAX(FREQUENCY(p,p))=1)
)
),
LET(
b,IF(IO(values),r,JAU(r,v,FILTER(INDEX(d,3),INDEX(d,2)="v"))),
e,IF(IO(counts),r,JAV(r,c,FILTER(INDEX(d,3),INDEX(d,2)="c"))),
f,IF(IO(types),r,JAW(r,t,FILTER(INDEX(d,3),INDEX(d,2)="t"),XTYPE(reference,3))),
g,IF(IO(lengths),r,JAX(r,l,FILTER(INDEX(d,3),INDEX(d,2)="l"))),
m,LET(
x,HSTACK(
IF(IO(priorities),"",p),
IFERROR(FILTER({1,2,3,4},ISNUMBER(MATCH({1,2,3,4},p,0))=FALSE),"")
),
y,FILTER(x,x<>""),
IF(
ISNUMBER(MATCH(y,{1,2,3,4},0)),
INDEX({"b","e","f","g"},MATCH(y,{1,2,3,4},0)),
y
)
),
JAZ(m,r,b,e,f,g)
),
NA()
)
)
)
)
JAY (parameter-processing core for XREPLACE)
Excel Formula:
=LAMBDA(values,counts,types,lengths,replacements,
LET(
v,JBA(values),
c,counts,
t,types,
l,lengths,
s,JBA(replacements),
a,VSTACK(
HSTACK(
IF(ISERROR(v),v,IF(v="","",v)),
IF(c="","",c),
IF(t="","",t),
IF(l="","",l)
),
HSTACK(
LET(x,IFERROR(COLUMNS(v),0),IF(x=0,0,IF(SEQUENCE(,x),"v",""))),
LET(x,IFERROR(COLUMNS(c),0),IF(x=0,0,IF(SEQUENCE(,x),"c",""))),
LET(x,IFERROR(COLUMNS(t),0),IF(x=0,0,IF(SEQUENCE(,x),"t",""))),
LET(x,IFERROR(COLUMNS(l),0),IF(x=0,0,IF(SEQUENCE(,x),"l","")))
)
),
VSTACK(
CHOOSECOLS(a,
FILTER(
SEQUENCE(,COLUMNS(a)),
ISTEXT(INDEX(a,2))
)
),
s
)
)
)
JAZ (prioritized-replacing core for XREPLACE)
Excel Formula:
=LAMBDA(order,reference,value_replacement,count_replacement,type_replacement,length_replacement,
LET(
m,order,
r,reference,
b,value_replacement,
e,count_replacement,
f,type_replacement,
g,length_replacement,
h,CHOOSE(MATCH(INDEX(m,,1),{"b","e","f","g"},0),b,e,f,g),
i,CHOOSE(MATCH(INDEX(m,,2),{"b","e","f","g"},0),b,e,f,g),
j,CHOOSE(MATCH(INDEX(m,,3),{"b","e","f","g"},0),b,e,f,g),
k,CHOOSE(MATCH(INDEX(m,,4),{"b","e","f","g"},0),b,e,f,g),
IF(
IFERROR(h,ERROR.TYPE(h))=IFERROR(r,ERROR.TYPE(r)),
IF(
IFERROR(i,ERROR.TYPE(i))=IFERROR(r,ERROR.TYPE(r)),
IF(
IFERROR(j,ERROR.TYPE(j))=IFERROR(r,ERROR.TYPE(r)),
IF(
IFERROR(k,ERROR.TYPE(k))=IFERROR(r,ERROR.TYPE(r)),
r,
k
),
j
),
i
),
h
)
)
)
JAU (by-value replacement core for XREPLACE)
Excel Formula:
=LAMBDA(reference,values,replacements,
LET(
r,reference,
v,JBA(values),
w,IF(
ISERROR(r),
MATCH(ERROR.TYPE(r),ERROR.TYPE(v),0),
MATCH(r,v,0)
),
IF(
ISNUMBER(w),
INDEX(replacements,w),
r
)
)
)
JAV (by-count replacement core for XREPLACE)
Excel Formula:
=LAMBDA(reference,counts,replacements,
LET(
r,reference,
c,LEN(TRIM(r)),
d,JBA(counts),
s,IF(
ISERROR(r),
LET(x,ERROR.TYPE(r),MAP(x,LAMBDA(a,ST1F0(x=a)))),
IF(
c>0,
IF(
r=FALSE,
MAP(r,LAMBDA(a,ST1F0(TEXT(r,10)="FALSE"))),
MAP(r,LAMBDA(a,ST1F0(r=a)))
),
MAP(r,LAMBDA(a,ST1F0(r="")))
)
),
t,IF(ISERROR(MATCH(s,d,0)),"",s),
u,IF(ISNUMBER(MATCH(t,d,0)),INDEX(replacements,MATCH(t,d,0)),t),
IF(t="",r,u)
)
)
JAW (by-type replacement core for XREPLACE)
Excel Formula:
=LAMBDA(reference,types,replacements,advanced_types_array,
LET(
d,JBA(types),
IF(
OR(
AND(
ST1F0(NOT(ISERROR(MATCH({31,32,33,34},d,0)))),
ST1F0(NOT(ISERROR(MATCH(3,d,0))))
),
AND(
ST1F0(NOT(ISERROR(MATCH({41,42},d,0)))),
ST1F0(NOT(ISERROR(MATCH(4,d,0))))
),
AND(
ST1F0(NOT(ISERROR(MATCH(ErrorTs,d,0)))),
ST1F0(NOT(ISERROR(MATCH(16,d,0))))
)
),
NA(),
LET(
e,FILTER(d,NOT(ISERROR(MATCH(d,{3,4,16},0)))),
s,advanced_types_array,
p,SWITCH(s,1,1,2,2,3,3,4,4,31,3,32,3,33,3,34,3,41,4,42,4,16),
t,IF(ISERROR(MATCH(s,d,0)),"",s),
u,IF(
ISNUMBER(MATCH(t,d,0)),
INDEX(replacements,MATCH(t,d,0)),
t
),
v,IF(ISERROR(MATCH(p,d,0)),"",p),
w,IF(
ISNUMBER(MATCH(v,d,0)),
INDEX(replacements,MATCH(v,d,0)),
v
),
y,IF(u="",w,u),
x,IF(t="",v,t),
IF(x="",reference,y)
)
)
)
)
JAX (by-length replacement core for XREPLACE)
Excel Formula:
=LAMBDA(reference,lengths,replacements,
LET(
p,reference,
q,IFERROR(p,""),
r,IF(ISTEXT(q),q,""),
s,LET(x,LEN(r),IF(x=0,"",x)),
a,JBA(lengths),
t,IF(
ISNUMBER(MATCH(s,a,0)),
INDEX(replacements,MATCH(s,a,0)),
""
),
u,IF(ISERROR(MATCH(s,a,0)),"",s),
IF(u="",p,t)
)
)
JBA (inserts single values into array brackets "{}")
Excel Formula:
=LAMBDA(reference[if_omitted],
IF(
IO(reference),
if_omitted,
LET(
r,reference,
IF(
AND(COLUMNS(IFERROR(r,""))=1,ISERROR(r)),
CHOOSECOLS(CHOOSE({1,2},r,r),1),
FILTER(r,SEQUENCE(,COLUMNS(r)))
)
)
)
)
XERROR.TYPE (posted separately)
XTYPE (posted separately)
AllTypes
Excel Formula:
={1,2,3,4,16,31,32,33,34,41,42,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614,1619}
IO (shortened form of ISOMITTED)
Excel Formula:
=LAMBDA(parameter,
ISOMITTED(parameter)
)
PLMN (stands for "parameter limit, multiple natural")
(Returns the parameter if it is at least one natural number (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered, i.e. wrong data type)
Excel Formula:
=LAMBDA(parameter,
LET(
o,IF(
OR(JAO(parameter),JAP(parameter)),
NA(),
IF(
ISNUMBER(parameter),
IF(
OR(MOD(parameter,1),parameter<=0),
NA(),
parameter
),
NA()
)
),
IF(
ST1F0(ISERROR(o)),
NA(),
o
)
)
)
PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
Excel Formula:
=LAMBDA(parameter,
INDEX(
IF(
OR(JAN(parameter),JAO(parameter),JAP(parameter)),
NA(),
parameter
),
1
)
)
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
Excel Formula:
=LAMBDA([parameter],
IF(
IO(parameter),
0,
parameter
)
)
T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
Excel Formula:
=LAMBDA(expression,
IF(
ISNUMBER(expression)+ISLOGICAL(expression),
IF(expression,1,0),
NA()
)
)
ST1F0 (Returns the sum of all entries within the reference that can be logically evaluated to 1 (TRUE), while considering all other entries as zeros)
Excel Formula:
=LAMBDA(reference,
SUM(T1F0(IFERROR(reference,0))
)
)
JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
Excel Formula:
=LAMBDA([reference],
LET(
r,IFERROR(
IF(reference="","",reference),
""
),
IF(
IO(reference),
FALSE,
IF(
ROWS(r)*COLUMNS(r)>1,
TRUE,
FALSE
)
)
)
)
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
Excel Formula:
=LAMBDA(reference,
IF(
IO(reference),
FALSE,
IF(
SUM(IF(ISERROR(reference),1,0)),
TRUE,
FALSE
)
)
)
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
Excel Formula:
=LAMBDA(reference,
IF(
IO(reference),
NA(),
IF(
SUM(IF(IFERROR(reference,0)="",1,0)),
TRUE,
FALSE)
)
)
XREPLACE.xlsx | ||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
1 | ||||||||||||||||||||||||||||||||||
2 | #CALC! | basic types | ||||||||||||||||||||||||||||||||
3 | types | 1 | dt:NUMBER | |||||||||||||||||||||||||||||||
4 | 23 | book | TRUE | #DIV/0! | note | 1 | 2 | 41 | 162 | 31 | 2 | 2 | dt:TEXT | |||||||||||||||||||||
5 | FALSE | 56.8 | 23 | #N/A | 42 | 33 | 1 | 32 | 1 | 167 | 3 | dt:BLANK | ||||||||||||||||||||||
6 | 0 | chair | 67 | notebook | #REF! | door | 1 | 2 | 1 | 2 | 164 | 2 | 4 | dt:LOGICAL | ||||||||||||||||||||
7 | 107 | book | TRUE | 7.5 | 34 | 1 | 31 | 2 | 41 | 1 | 16 | dt:ERROR | ||||||||||||||||||||||
8 | #VALUE! | 100 | book | #DIV/0! | 163 | 31 | 1 | 33 | 2 | 162 | ||||||||||||||||||||||||
9 | advanced types | |||||||||||||||||||||||||||||||||
10 | 1 | dt:NUMBER | ||||||||||||||||||||||||||||||||
11 | single value | 123456 | book | TRUE | #DIV/0! | note | 23 | 123456 | TRUE | #DIV/0! | note | 23 | book | OOOOO | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | note | 2 | dt:TEXT | |||||||||||
12 | FALSE | 56.8 | 123456 | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | TRUE | 56.8 | 23 | #N/A | 31 | dt:BLANK_REAL | ||||||||||||||||
13 | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 32 | dt:BLANK_FORMULA | ||||||||
14 | 107 | book | TRUE | 7.5 | 107 | 123456 | TRUE | 7.5 | 107 | book | OOOOO | 7.5 | 107 | book | TRUE | 7.5 | 33 | dt:BLANK_SPACE | ||||||||||||||||
15 | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | 123456 | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | 34 | dt:BLANK_PASTED | ||||||||||||||||
16 | 41 | dt:LOGICAL_TRUE | ||||||||||||||||||||||||||||||||
17 | 23 | book | TRUE | #DIV/0! | #DIV/0! | note | 23 | book | TRUE | 123456 | note | 23 | book | TRUE | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | note | 42 | dt:LOGICAL_FALSE | |||||||||||
18 | FALSE | #DIV/0! | 56.8 | #DIV/0! | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #NULL! | 161 | dt:ERROR_NULL! | ||||||||||||||
19 | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | FALSE | door | 0 | chair | 67 | notebook | #REF! | door | 162 | dt:ERROR_DIV/0! | ||||||||
20 | #DIV/0! | 107 | #DIV/0! | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 163 | dt:ERROR_VALUE! | ||||||||||||||
21 | #VALUE! | #DIV/0! | 100 | #DIV/0! | book | #DIV/0! | #VALUE! | 100 | book | 123456 | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | 164 | dt:ERROR_REF! | ||||||||||||||
22 | 165 | dt:ERROR_NAME? | ||||||||||||||||||||||||||||||||
23 | 166 | dt:ERROR_NUM! | ||||||||||||||||||||||||||||||||
24 | single count | 23 | book | TRUE | #DIV/0! | TRUE | OOOOO | book | OOOOO | OOOOO | note | 23 | TRUE | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | #DIV/0! | note | 167 | dt:ERROR_N/A | |||||||||||
25 | TRUE | TRUE | 23 | TRUE | FALSE | 56.8 | OOOOO | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | #DIV/0! | 56.8 | #DIV/0! | 23 | #N/A | 168 | dt:ERROR_GETTING_DATA | ||||||||||||||
26 | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 169 | dt:ERROR_SPILL! | ||||||||
27 | TRUE | book | TRUE | TRUE | 107 | book | OOOOO | 7.5 | 107 | TRUE | 7.5 | #DIV/0! | 107 | #DIV/0! | book | TRUE | 7.5 | 1610 | dt:ERROR_CONNECT! | |||||||||||||||
28 | TRUE | TRUE | book | #DIV/0! | #VALUE! | 100 | book | OOOOO | #VALUE! | 100 | #DIV/0! | #VALUE! | #DIV/0! | 100 | #DIV/0! | book | #DIV/0! | 1611 | dt:ERROR_BLOCKED! | |||||||||||||||
29 | 1612 | dt:ERROR_UNKNOWN! | ||||||||||||||||||||||||||||||||
30 | 1613 | dt:ERROR_FIELD! | ||||||||||||||||||||||||||||||||
31 | single type | OOOOO | book | TRUE | #DIV/0! | note | 23 | 12456 | TRUE | #DIV/0! | 12456 | 23 | book | TRUE | #DIV/0! | TRUE | note | 23 | book | #N/A | #DIV/0! | note | 1614 | dt:ERROR_CALC! | ||||||||||
32 | FALSE | OOOOO | OOOOO | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | TRUE | 56.8 | TRUE | 23 | #N/A | #N/A | 56.8 | 23 | #N/A | 1619 | dt:ERROR_PYTHON! | ||||||||||||||
33 | OOOOO | chair | OOOOO | notebook | #REF! | door | 0 | 12456 | 67 | 12456 | #REF! | 12456 | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | ||||||||||
34 | OOOOO | book | TRUE | OOOOO | 107 | 12456 | TRUE | 7.5 | TRUE | 107 | TRUE | book | TRUE | 7.5 | 107 | book | #N/A | 7.5 | ||||||||||||||||
35 | #VALUE! | OOOOO | book | #DIV/0! | #VALUE! | 100 | 12456 | #DIV/0! | #VALUE! | TRUE | 100 | TRUE | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | ||||||||||||||||
36 | ||||||||||||||||||||||||||||||||||
37 | 23 | book | TRUE | 123456 | note | 23 | book | TRUE | #DIV/0! | OOOOO | note | 23 | book | TRUE | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | note | |||||||||||||
38 | FALSE | 56.8 | 23 | 123456 | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | FALSE | 23 | #N/A | FALSE | #REF! | 56.8 | 23 | #N/A | ||||||||||||||||
39 | 0 | chair | 67 | notebook | 123456 | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | ||||||||||
40 | 107 | book | TRUE | 7.5 | 107 | OOOOO | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | |||||||||||||||||
41 | 123456 | 100 | book | 123456 | #VALUE! | OOOOO | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | #REF! | book | #DIV/0! | ||||||||||||||||
42 | ||||||||||||||||||||||||||||||||||
43 | 23 | book | TRUE | #DIV/0! | note | 23 | book | FALSE | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | note | 23 | book | TRUE | note | |||||||||||||||
44 | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | 123456 | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | ||||||||||||||||||
45 | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | 0 | chair | 67 | notebook | #REF! | door | ||||||||||
46 | TRUE | 107 | book | TRUE | 7.5 | 107 | book | FALSE | 7.5 | 107 | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | |||||||||||||||||
47 | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | |||||||||||||||||||
48 | ||||||||||||||||||||||||||||||||||
49 | ||||||||||||||||||||||||||||||||||
50 | single length | 23 | 123456 | TRUE | #DIV/0! | 123456 | 23 | book | TRUE | #DIV/0! | note | 23 | book | TRUE | #DIV/0! | note | 23 | #REF! | TRUE | #DIV/0! | #REF! | |||||||||||||
51 | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | FALSE | 56.8 | 23 | #N/A | ||||||||||||||||||
52 | 0 | chair | 67 | notebook | #REF! | 123456 | 0 | 67 | notebook | #REF! | door | 0 | chair | 67 | FALSE | #REF! | door | 0 | chair | 67 | notebook | #REF! | #REF! | |||||||||||
53 | 107 | 123456 | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 107 | book | TRUE | 7.5 | 107 | #REF! | TRUE | 7.5 | ||||||||||||||||||
54 | #VALUE! | 100 | 123456 | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | book | #DIV/0! | #VALUE! | 100 | #REF! | #DIV/0! | ||||||||||||||||||
55 | ||||||||||||||||||||||||||||||||||
XREPLACE-1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =LAMBDA(reference,[values],[counts],[types],[lengths],[replacements],[priorities],LET(r,IF(LEN(TRIM(reference))=0,"",reference),v,values,c,counts,t,types,l,lengths,s,replacements,p,priorities,d,JAY(v,c,t,l,s),IF(IFERROR(ROWS(d),1)=1,IF(ERROR.TYPE(d)=14,IF(IFERROR(COLUMNS(replacements),0)=0,r,NA()),NA()),IF(AND(SUM(IF(IO(values),0,IFERROR(COLUMNS(v),1)),IF(IO(counts),0,COLUMNS(c)),IF(IO(types),0,COLUMNS(t)),IF(IO(lengths),0,COLUMNS(l)))=IFERROR(COLUMNS(replacements),1),IF(IO(counts),1,AND(MAX(FREQUENCY(c,c))=1,NOT(ISERROR(PLMN(c))))),IF(IO(types),1,AND(MAX(FREQUENCY(t,t))=1,NOT(ISERROR(MATCH(t,AllTypes,0))))),IF(IO(lengths),1,AND(MAX(FREQUENCY(l,l))=1,NOT(ISERROR(PLMN(l))))),IF(IO(priorities),1,AND(NOT(ISERROR(MATCH(p,{1,2,3,4},0))),MAX(FREQUENCY(p,p))=1))),LET(b,IF(IO(values),r,JAU(r,v,FILTER(INDEX(d,3),INDEX(d,2)="v"))),e,IF(IO(counts),r,JAV(r,c,FILTER(INDEX(d,3),INDEX(d,2)="c"))),f,IF(IO(types),r,JAW(r,t,FILTER(INDEX(d,3),INDEX(d,2)="t"),XTYPE(reference,3))),g,IF(IO(lengths),r,JAX(r,l,FILTER(INDEX(d,3),INDEX(d,2)="l"))),m,LET(x,HSTACK(IF(IO(priorities),"",p),IFERROR(FILTER({1,2,3,4},ISNUMBER(MATCH({1,2,3,4},p,0))=FALSE),"")),y,FILTER(x,x<>""),IF(ISNUMBER(MATCH(y,{1,2,3,4},0)),INDEX({"b","e","f","g"},MATCH(y,{1,2,3,4},0)),y)),JAZ(m,r,b,e,f,g)),NA())))) |
I4:N8 | I4 | =XTYPE(B4:G8,3) |
E4,G8 | E4 | =1/0 |
E5 | E5 | =IF(1,"") |
G5 | G5 | =#N/A |
B11:G15 | B11 | =XREPLACE(B4:G8,23,,,,123456) |
I11:N15 | I11 | =XREPLACE(B4:G8,"book",,,,123456) |
P11:U15 | P11 | =XREPLACE(B4:G8,TRUE,,,,"OOOOO") |
W11:AB15 | W11 | =XREPLACE(B4:G8,FALSE,,,,TRUE) |
B17:G21 | B17 | =XREPLACE(B4:G8,"",,,,#DIV/0!) |
I17:N21 | I17 | =XREPLACE(B4:G8,#DIV/0!,,,,123456) |
P17:U21 | P17 | =XREPLACE(B4:G8,#REF!,,,,FALSE) |
W17:AB21 | W17 | =XREPLACE(B4:G8,#N/A,,,,#NULL!) |
B24:G28 | B24 | =XREPLACE(B4:G8,,1,,,TRUE) |
I24:N28 | I24 | =XREPLACE(B4:G8,,2,,,"OOOOO") |
P24:U28 | P24 | =XREPLACE(B4:G8,,3,,,"") |
W24:AB28 | W24 | =XREPLACE(B4:G8,,7,,,#DIV/0!) |
B31:G35 | B31 | =XREPLACE(B4:G8,,,1,,"OOOOO") |
I31:N35 | I31 | =XREPLACE(B4:G8,,,2,,12456) |
P31:U35 | P31 | =XREPLACE(B4:G8,,,3,,TRUE) |
W31:AB35 | W31 | =XREPLACE(B4:G8,,,4,,#N/A) |
B37:G41 | B37 | =XREPLACE(B4:G8,,,16,,123456) |
I37:N41 | I37 | =XREPLACE(B4:G8,,,31,,"OOOOO") |
P37:U41 | P37 | =XREPLACE(B4:G8,,,32,,FALSE) |
W37:AB41 | W37 | =XREPLACE(B4:G8,,,33,,#REF!) |
B43:G47 | B43 | =XREPLACE(B4:G8,,,34,,TRUE) |
I43:N47 | I43 | =XREPLACE(B4:G8,,,41,,FALSE) |
P43:U47 | P43 | =XREPLACE(B4:G8,,,42,,123456) |
W43:AB47 | W43 | =XREPLACE(B4:G8,,,162,,"") |
B50:G54 | B50 | =XREPLACE(B4:G8,,,,4,123456) |
I50:N54 | I50 | =XREPLACE(B4:G8,,,,5,"") |
P50:U54 | P50 | =XREPLACE(B4:G8,,,,8,FALSE) |
W50:AB54 | W50 | =XREPLACE(B4:G8,,,,4,#REF!) |
Dynamic array formulas. |
Upvote
0