XREPLACE

XREPLACE(reference,[values],[counts],[types],[lengths],[replacements],[priorities])
reference
Required. Specifies the function input and can be a cell, range, or array.
values
Optional. Specifies the value(s) of any data type intended to be replaced.
counts
Optional. Specifies the count(s) of values of any data type intended to be replaced and can be any natural number(s).
types
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; and 1619, for #PYTHON! error.
lengths
Optional. Specifies the character length(s) of string value(s) (i.e. texts) intended to be replaced and can be any natural number(s).
replacements
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).
priorities
Optional. Specifies the priorities of the four categories of replacements to be performed and takes any or all of four arguments: 1, for value-based replacement; 2, for count-based replacement; 3, for type-based replacement; 4, for length-based replacement.
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.
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2#CALC!basic types
3types1dt:NUMBER
423bookTRUE#DIV/0!note12411623122dt:TEXT
5FALSE 56.8 23#N/A423313211673dt:BLANK
60chair67notebook#REF!door121216424dt:LOGICAL
7107bookTRUE7.534131241116dt:ERROR
8#VALUE!100 book#DIV/0!163311332162
9advanced types
101dt:NUMBER
11single value123456bookTRUE#DIV/0!note23123456TRUE#DIV/0!note23bookOOOOO#DIV/0!note23bookTRUE#DIV/0!note2dt:TEXT
12FALSE56.8123456#N/AFALSE56.823#N/AFALSE56.823#N/ATRUE56.823#N/A31dt:BLANK_REAL
130chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door32dt:BLANK_FORMULA
14107bookTRUE7.5107123456TRUE7.5107bookOOOOO7.5107bookTRUE7.533dt:BLANK_SPACE
15#VALUE!100book#DIV/0!#VALUE!100123456#DIV/0!#VALUE!100book#DIV/0!#VALUE!100book#DIV/0!34dt:BLANK_PASTED
1641dt:LOGICAL_TRUE
1723bookTRUE#DIV/0!#DIV/0!note23bookTRUE123456note23bookTRUE#DIV/0!note23bookTRUE#DIV/0!note42dt:LOGICAL_FALSE
18FALSE#DIV/0!56.8#DIV/0!23#N/AFALSE56.823#N/AFALSE56.823#N/AFALSE56.823#NULL!161dt:ERROR_NULL!
190chair67notebook#REF!door0chair67notebook#REF!door0chair67notebookFALSEdoor0chair67notebook#REF!door162dt:ERROR_DIV/0!
20#DIV/0!107#DIV/0!bookTRUE7.5107bookTRUE7.5107bookTRUE7.5107bookTRUE7.5163dt:ERROR_VALUE!
21#VALUE!#DIV/0!100#DIV/0!book#DIV/0!#VALUE!100book123456#VALUE!100book#DIV/0!#VALUE!100book#DIV/0!164dt:ERROR_REF!
22165dt:ERROR_NAME?
23166dt:ERROR_NUM!
24single count23bookTRUE#DIV/0!TRUEOOOOObookOOOOOOOOOOnote23TRUE#DIV/0!note23bookTRUE#DIV/0!#DIV/0!note167dt:ERROR_N/A
25TRUETRUE23TRUEFALSE56.8OOOOO#N/AFALSE56.823#N/AFALSE#DIV/0!56.8#DIV/0!23#N/A168dt:ERROR_GETTING_DATA
26TRUETRUETRUETRUETRUETRUE0chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door169dt:ERROR_SPILL!
27TRUEbookTRUETRUE107bookOOOOO7.5107TRUE7.5#DIV/0!107#DIV/0!bookTRUE7.51610dt:ERROR_CONNECT!
28TRUETRUEbook#DIV/0!#VALUE!100bookOOOOO#VALUE!100#DIV/0!#VALUE!#DIV/0!100#DIV/0!book#DIV/0!1611dt:ERROR_BLOCKED!
291612dt:ERROR_UNKNOWN!
301613dt:ERROR_FIELD!
31single typeOOOOObookTRUE#DIV/0!note2312456TRUE#DIV/0!1245623bookTRUE#DIV/0!TRUEnote23book#N/A#DIV/0!note1614dt:ERROR_CALC!
32FALSEOOOOOOOOOO#N/AFALSE56.823#N/AFALSETRUE56.8TRUE23#N/A#N/A56.823#N/A1619dt:ERROR_PYTHON!
33OOOOOchairOOOOOnotebook#REF!door0124566712456#REF!124560chair67notebook#REF!door0chair67notebook#REF!door
34OOOOObookTRUEOOOOO10712456TRUE7.5TRUE107TRUEbookTRUE7.5107book#N/A7.5
35#VALUE!OOOOObook#DIV/0!#VALUE!10012456#DIV/0!#VALUE!TRUE100TRUEbook#DIV/0!#VALUE!100book#DIV/0!
36
3723bookTRUE123456note23bookTRUE#DIV/0!OOOOOnote23bookTRUE#DIV/0!note23bookTRUE#DIV/0!note
38FALSE56.823123456FALSE56.823#N/AFALSE56.8FALSE23#N/AFALSE#REF!56.823#N/A
390chair67notebook123456door0chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door
40107bookTRUE7.5107OOOOObookTRUE7.5107bookTRUE7.5107bookTRUE7.5
41123456100book123456#VALUE!OOOOO100book#DIV/0!#VALUE!100book#DIV/0!#VALUE!100#REF!book#DIV/0!
42
4323bookTRUE#DIV/0!note23bookFALSE#DIV/0!note23bookTRUE#DIV/0!note23bookTRUEnote
44FALSE56.823#N/AFALSE56.823#N/A12345656.823#N/AFALSE56.823#N/A
450chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door
46TRUE107bookTRUE7.5107bookFALSE7.5107bookTRUE7.5107bookTRUE7.5
47#VALUE!100book#DIV/0!#VALUE!100book#DIV/0!#VALUE!100book#DIV/0!#VALUE!100book
48
49
50single length23123456TRUE#DIV/0!12345623bookTRUE#DIV/0!note23bookTRUE#DIV/0!note23#REF!TRUE#DIV/0!#REF!
51FALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/A
520chair67notebook#REF!123456067notebook#REF!door0chair67FALSE#REF!door0chair67notebook#REF!#REF!
53107123456TRUE7.5107bookTRUE7.5107bookTRUE7.5107#REF!TRUE7.5
54#VALUE!100123456#DIV/0!#VALUE!100book#DIV/0!#VALUE!100book#DIV/0!#VALUE!100#REF!#DIV/0!
55
XREPLACE-1
Cell Formulas
RangeFormula
B2B2=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:N8I4=XTYPE(B4:G8,3)
E4,G8E4=1/0
E5E5=IF(1,"")
G5G5=#N/A
B11:G15B11=XREPLACE(B4:G8,23,,,,123456)
I11:N15I11=XREPLACE(B4:G8,"book",,,,123456)
P11:U15P11=XREPLACE(B4:G8,TRUE,,,,"OOOOO")
W11:AB15W11=XREPLACE(B4:G8,FALSE,,,,TRUE)
B17:G21B17=XREPLACE(B4:G8,"",,,,#DIV/0!)
I17:N21I17=XREPLACE(B4:G8,#DIV/0!,,,,123456)
P17:U21P17=XREPLACE(B4:G8,#REF!,,,,FALSE)
W17:AB21W17=XREPLACE(B4:G8,#N/A,,,,#NULL!)
B24:G28B24=XREPLACE(B4:G8,,1,,,TRUE)
I24:N28I24=XREPLACE(B4:G8,,2,,,"OOOOO")
P24:U28P24=XREPLACE(B4:G8,,3,,,"")
W24:AB28W24=XREPLACE(B4:G8,,7,,,#DIV/0!)
B31:G35B31=XREPLACE(B4:G8,,,1,,"OOOOO")
I31:N35I31=XREPLACE(B4:G8,,,2,,12456)
P31:U35P31=XREPLACE(B4:G8,,,3,,TRUE)
W31:AB35W31=XREPLACE(B4:G8,,,4,,#N/A)
B37:G41B37=XREPLACE(B4:G8,,,16,,123456)
I37:N41I37=XREPLACE(B4:G8,,,31,,"OOOOO")
P37:U41P37=XREPLACE(B4:G8,,,32,,FALSE)
W37:AB41W37=XREPLACE(B4:G8,,,33,,#REF!)
B43:G47B43=XREPLACE(B4:G8,,,34,,TRUE)
I43:N47I43=XREPLACE(B4:G8,,,41,,FALSE)
P43:U47P43=XREPLACE(B4:G8,,,42,,123456)
W43:AB47W43=XREPLACE(B4:G8,,,162,,"")
B50:G54B50=XREPLACE(B4:G8,,,,4,123456)
I50:N54I50=XREPLACE(B4:G8,,,,5,"")
P50:U54P50=XREPLACE(B4:G8,,,,8,FALSE)
W50:AB54W50=XREPLACE(B4:G8,,,,4,#REF!)
Dynamic array formulas.
 
Upvote 0
Additional more complex examples for 1) multiple elements within each category of replacement and 2) single elements within multiple replacement categories, while also demonstrating the use of the "priorities" feature of XREPLACE:

XREPLACE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2#CALC!basic types
3types1dt:NUMBER
423bookTRUE#DIV/0!note12411623122dt:TEXT
5FALSE 56.8 23#N/A423313211673dt:BLANK
60chair67notebook#REF!door121216424dt:LOGICAL
7107bookTRUE7.534131241116dt:ERROR
8#VALUE!100 book#DIV/0!163311332162
9advanced types
101dt:NUMBER
11multiples of each category123456OOOOOTRUETRUEnoteFALSE#DIV/0!FALSEFALSEGGGGG23999999TRUEFALSE99999923#VALUE!TRUE#DIV/0!#VALUE!2dt:TEXT
12FALSE56.8123456#N/AGGGGGGGGGGFALSEGGGGGFALSE56.823FALSE56.823#N/A31dt:BLANK_REAL
130chair67notebook#REF!doorGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG0999999679999999999990AAAAA67555555#REF!#VALUE!32dt:BLANK_FORMULA
14107OOOOOTRUE7.5GGGGG#DIV/0!FALSEGGGGG107FALSE999999TRUE7.5107#VALUE!TRUE7.533dt:BLANK_SPACE
15#VALUE!100OOOOOTRUEGGGGGGGGGG#DIV/0!FALSEFALSE100999999#VALUE!100#VALUE!#DIV/0!34dt:BLANK_PASTED
1641dt:LOGICAL_TRUE
1742dt:LOGICAL_FALSE
18singles of multiple categories123456bookOOOOOOOOOOnote123456bookOOOOOOOOOOnoteOOOOObookOOOOOOOOOOnoteOOOOObookOOOOOOOOOOnote161dt:ERROR_NULL!
19FALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8OOOOO#N/AFALSE56.8OOOOO#N/A162dt:ERROR_DIV/0!
200chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door0chair67notebook#REF!door163dt:ERROR_VALUE!
21107bookOOOOO7.5107bookOOOOO7.5107bookOOOOO7.5107bookOOOOO7.5164dt:ERROR_REF!
22#VALUE!100bookOOOOO#VALUE!100bookOOOOO#VALUE!100bookOOOOO#VALUE!100bookOOOOO165dt:ERROR_NAME?
23166dt:ERROR_NUM!
24FALSEbookFALSEFALSEnoteFALSEbookFALSEFALSEnote999999bookFALSEFALSEnote999999bookFALSEFALSEnote167dt:ERROR_N/A
25FALSE999999FALSE#N/AFALSE999999FALSE#N/AFALSE999999999999#N/AFALSE999999999999#N/A168dt:ERROR_GETTING_DATA
26999999chair999999notebook#REF!door999999chair999999notebook#REF!door999999chair999999notebook#REF!door999999chair999999notebook#REF!door169dt:ERROR_SPILL!
27999999bookFALSE999999999999bookFALSE999999999999bookFALSE999999999999bookFALSE9999991610dt:ERROR_CONNECT!
28#VALUE!999999bookFALSE#VALUE!999999bookFALSE#VALUE!999999bookFALSE#VALUE!999999bookFALSE1611dt:ERROR_BLOCKED!
291612dt:ERROR_UNKNOWN!
3023VVVVVTRUE#DIV/0!VVVVV23VVVVVTRUE#DIV/0!VVVVV23123456TRUE#DIV/0!12345623123456TRUE#DIV/0!1234561613dt:ERROR_FIELD!
31FALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/A1614dt:ERROR_CALC!
320VVVVV67VVVVV#REF!VVVVV0VVVVV67VVVVV#REF!VVVVV0VVVVV67VVVVV#REF!1234560VVVVV67VVVVV#REF!1234561619dt:ERROR_PYTHON!
33107VVVVVTRUE7.5107VVVVVTRUE7.5107123456TRUE7.5107123456TRUE7.5
34#VALUE!100VVVVV#DIV/0!#VALUE!100VVVVV#DIV/0!#VALUE!100123456#DIV/0!#VALUE!100123456#DIV/0!
35
3623TRUETRUE#DIV/0!23TRUETRUE#DIV/0!23TRUE#DIV/0!23TRUE#DIV/0!
37FALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/AFALSE56.823#N/A
380chair67notebook#REF!0chair67notebook#REF!0chair67notebook#REF!0chair67notebook#REF!
39107TRUETRUE7.5107TRUETRUE7.5107TRUE7.5107TRUE7.5
40#VALUE!100TRUE#DIV/0!#VALUE!100TRUE#DIV/0!#VALUE!100#DIV/0!#VALUE!100#DIV/0!
41
42123456888888OOOOO123456888888123456888888OOOOO123456888888123456888888123456123456888888123456MMMMM123456123456MMMMM
43FALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/A
44088888867888888#REF!888888088888867888888#REF!888888088888867888888#REF!888888088888867888888#REF!MMMMM
45107888888OOOOO7.5107888888OOOOO7.51078888881234567.5107MMMMM1234567.5
46#VALUE!100888888123456#VALUE!100888888123456#VALUE!100888888123456#VALUE!100MMMMM123456
47
48123456888888OOOOO123456888888123456888888OOOOO123456888888123456MMMMMOOOOO123456MMMMM123456MMMMMOOOOO123456MMMMM
49FALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/A
50088888867888888#REF!888888088888867888888#REF!888888088888867888888#REF!MMMMM088888867888888#REF!MMMMM
51107888888OOOOO7.5107888888OOOOO7.5107MMMMMOOOOO7.5107MMMMMOOOOO7.5
52#VALUE!100888888123456#VALUE!100888888123456#VALUE!100MMMMM123456#VALUE!100MMMMM123456
53
54123456MMMMMOOOOO123456MMMMM123456MMMMM123456123456MMMMM123456MMMMM123456123456MMMMM123456MMMMMOOOOO123456MMMMM
55FALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/AFALSE56.8123456#N/A
56088888867888888#REF!MMMMM088888867888888#REF!MMMMM088888867888888#REF!MMMMM088888867888888#REF!MMMMM
57107MMMMMOOOOO7.5107MMMMM1234567.5107MMMMM1234567.5107MMMMMOOOOO7.5
58#VALUE!100MMMMM123456#VALUE!100MMMMM123456#VALUE!100MMMMM123456#VALUE!100MMMMM123456
59
XREPLACE-2
Cell Formulas
RangeFormula
B2B2=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:N8I4=XTYPE(B4:G8,3)
E4,G8E4=1/0
E5E5=IF(1,"")
G5G5=#N/A
B11:G15B11=XREPLACE(B4:G8,{23,"book",#DIV/0!},,,,{123456,"OOOOO",TRUE})
I11:N15I11=XREPLACE(B4:G8,,{3,1,2},,,{#DIV/0!,"GGGGG",FALSE})
P11:U15P11=XREPLACE(B4:G8,,,{2,31,16},,{999999,FALSE,""})
W11:AB15W11=XREPLACE(B4:G8,,,,{4,8,5},{#VALUE!,555555,"AAAAA"})
B18:G22B18=XREPLACE(B4:G8,23,2,,,{123456,"OOOOO"})
I18:N22I18=XREPLACE(B4:G8,23,2,,,{123456,"OOOOO"},{1,2})
P18:U22P18=XREPLACE(B4:G8,23,2,,,{123456,"OOOOO"},{2,1})
W18:AB22W18=XREPLACE(B4:G8,23,2,,,{123456,"OOOOO"},{2})
B24:G28B24=XREPLACE(B4:G8,,2,1,,{FALSE,999999})
I24:N28I24=XREPLACE(B4:G8,,2,1,,{FALSE,999999},{2,3})
P24:U28P24=XREPLACE(B4:G8,,2,1,,{FALSE,999999},{3,2})
W24:AB28W24=XREPLACE(B4:G8,,2,1,,{FALSE,999999},{3,2,1,4})
B30:G34B30=XREPLACE(B4:G8,,,2,4,{"VVVVV",123456})
I30:N34I30=XREPLACE(B4:G8,,,2,4,{"VVVVV",123456},{3,4})
P30:U34P30=XREPLACE(B4:G8,,,2,4,{"VVVVV",123456},{4,3})
W30:AB34W30=XREPLACE(B4:G8,,,2,4,{"VVVVV",123456},{4,3,2,1})
B36:G40B36=XREPLACE(B4:G8,"book",,,4,{TRUE,""})
I36:N40I36=XREPLACE(B4:G8,"book",,,4,{TRUE,""},{1,4})
P36:U40P36=XREPLACE(B4:G8,"book",,,4,{TRUE,""},{4,1})
W36:AB40W36=XREPLACE(B4:G8,"book",,,4,{TRUE,""},{4,1,2,3})
B42:G46B42=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"})
I42:N46I42=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{1,2,3,4})
P42:U46P42=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{2,1,3,4})
W42:AB46W42=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{2,1,4,3})
B48:G52B48=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{3,1,2,4})
I48:N52I48=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{3,1,4,2})
P48:U52P48=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{1,2,4,3})
W48:AB52W48=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{4,1,2,3})
B54:G58B54=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{4,1,3,2})
I54:N58I54=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{4,2,1,3})
P54:U58P54=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{4,2,3,1})
W54:AB58W54=XREPLACE(B4:G8,TRUE,2,2,4,{"OOOOO",123456,888888,"MMMMM"},{4,3,1,2})
Dynamic array formulas.
 
Updated code:
1) fixes the bug that caused certain priorities to be missed if the intended replacement value was an error or blank (see #3)
2) optimizes code
3) adds a new feature, through an "output" on/off parameter, for the four replacement core functions JAU, JAV, JAW, and JAX, such that if used on their own, they can now display the modified cells in a matrix of the same size as the input reference, stacked under the output. This matrix consists of zeros (for unmodified cells) and numbers 1 to n corresponding to the order of replacement values specified in the "replacements" parameters. This feature is used internally within the XREPLACE code to perform prioritized replacement (related to fix in #1)
4) adds the PLSL logical on/off switch helper function for #3

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,JBA(priorities),
      d,JAY(v,c,t,l,s),
      aa,IO(values),
      ab,IO(counts),
      ac,IO(types),
      ad,IO(lengths),
      ae,IO(priorities),
      IF(
         IFERROR(ROWS(d),1)=1,
         IF(
            ERROR.TYPE(d)=14,
            IF(IFERROR(COLUMNS(replacements),0)=0,r,NA()),
            NA()
         ),
         IF(
            AND(
               SUM(
                  IF(aa,0,IFERROR(COLUMNS(v),1)),
                  IF(ab,0,COLUMNS(c)),
                  IF(ac,0,COLUMNS(t)),
                  IF(ad,0,COLUMNS(l))
               )=IFERROR(COLUMNS(replacements),1),
               IF(
                  ab,
                  1,
                   AND(
                      MAX(FREQUENCY(c,c))=1,
                      NOT(ISERROR(PLMN(c)))
                   )
               ),
               IF(
                  ac,
                  1,
                  AND(
                     MAX(FREQUENCY(t,t))=1,
                     NOT(ISERROR(MATCH(t,AllTypes,0)))
                  )
               ),
               IF(
                  ad,
                  1,
                  AND(
                     MAX(FREQUENCY(l,l))=1,
                     NOT(ISERROR(PLMN(l)))
                  )
               ),
               IF(
                  ae,
                  1,
                  AND(
                     NOT(ISERROR(MATCH(p,{1,2,3,4},0))),
                     MAX(FREQUENCY(p,p))=1
                  )
               )
            ),
            LET(
               a,INDEX(d,2),
               o,INDEX(d,3),
               n,ROWS(r),
               q,SEQUENCE(n),
               z,q+n,
               u,IF(SEQUENCE(n,COLUMNS(r)),0),
               bb,IF(aa,r,JAU(r,v,FILTER(o,a="v"),1)),
               bc,IF(aa,,CHOOSEROWS(bb,z)),
               b,IF(aa,r,CHOOSEROWS(bb,q)),
               eb,IF(ab,r,JAV(r,c,FILTER(o,a="c"),1)),
               ec,IF(ab,,CHOOSEROWS(eb,z)),
               e,IF(ab,r,CHOOSEROWS(eb,q)),
               fb,IF(ac,r,JAW(r,t,FILTER(o,a="t"),XTYPE(reference,3),1)),
               fc,IF(ac,,CHOOSEROWS(fb,z)),
               f,IF(ac,r,CHOOSEROWS(fb,q)),
               gb,IF(ad,r,JAX(r,l,FILTER(o,a="l"),1)),
               gc,IF(ad,,CHOOSEROWS(gb,z)),
               g,IF(ad,r,CHOOSEROWS(gb,q)),
               m,LET(
                  x,HSTACK(
                     IF(ae,"",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
                  )
               ),
               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),
               JAZ(m,r,bc,ec,fc,gc,b,e,f,g)
            ),
            NA()
         )
      )
   )
)

JAZ (prioritized-replacing core for XREPLACE)
Excel Formula: 
=LAMBDA(order,reference,value_matrix,count_matrix,type_matrix,length_matrix,value_replacement,count_replacement,type_replacement,length_replacement,
   LET(
      m,order,
      r,reference,
      a,value_matrix,
      n,count_matrix,
      o,type_matrix,
      q,length_matrix,
      b,value_replacement,
      e,count_replacement,
      f,type_replacement,
      g,length_replacement,
      aa,INDEX(m,,1),
      ab,INDEX(m,,2),
      ac,INDEX(m,,3),
      ad,INDEX(m,,4),
      h,CHOOSE(MATCH(aa,{"b","e","f","g"},0),b,e,f,g),
      i,CHOOSE(MATCH(ab,{"b","e","f","g"},0),b,e,f,g),
      j,CHOOSE(MATCH(ac,{"b","e","f","g"},0),b,e,f,g),
      k,CHOOSE(MATCH(ad,{"b","e","f","g"},0),b,e,f,g),
      s,CHOOSE(MATCH(aa,{"b","e","f","g"},0),a,n,o,q),
      t,CHOOSE(MATCH(ab),{"b","e","f","g"},0),a,n,o,q),
      u,CHOOSE(MATCH(ac),{"b","e","f","g"},0),a,n,o,q),
      v,CHOOSE(MATCH(ad,{"b","e","f","g"},0),a,n,o,q),
      IF(
         s,
         h,
         IF(
            t,
            i,
            IF(
               u,
               j,
               IF(v,k,r)
            )
         )
      )
   )
)

JAU (by-value replacement core for XREPLACE)
Excel Formula: 
=LAMBDA(reference,values,replacements,[output],
   LET(
      r,reference,
      v,JBA(values),
      o,PLSL(output),
      w,IF(
         ISERROR(r),
         MATCH(ERROR.TYPE(r),ERROR.TYPE(v),0),
         MATCH(r,v,0)
      ),
      x,IF(
         ISNUMBER(w),
         INDEX(replacements,w),
         r
      ),
      IF(o,VSTACK(x,IFERROR(w,0)),x)
   )
)

JAV (by-count replacement core for XREPLACE)
Excel Formula: 
=LAMBDA(reference,counts,replacements,[output],
   LET(
      r,reference,
      c,LEN(TRIM(r)),
      d,JBA(counts),
      o,PLSL(output),
      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
      ),
      v,IF(t="",r,u),
      IF(o,VSTACK(v,IF(t="",0,t)),v)
   )
)

JAW (by-type replacement core for XREPLACE)
Excel Formula: 
=LAMBDA(reference,types,replacements,advanced_types_array,[output],
   LET(
      d,JBA(types),
      o,PLSL(output),
      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,LET(
               x,MATCH(t,d,0),
               IF(ISNUMBER(x),INDEX(replacements,x),t)
            ),
            v,IF(ISERROR(MATCH(p,d,0)),"",p),
            w,LET(
               x,MATCH(v,d,0),
               IF(ISNUMBER(x),INDEX(replacements,x),v)
            ),
            y,IF(u="",w,u),x,IF(t="",v,t),
            z,IF(x="",reference,y),
            IF(o,VSTACK(z,IF(x="",0,x)),z)
         )
      )
   )
)

JAX (by-length replacement core for XREPLACE)
Excel Formula: 
=LAMBDA(reference,lengths,replacements,[output],
   LET(
      p,reference,
      q,IFERROR(p,""),
      r,IF(ISTEXT(q),q,""),
      s,LET(x,LEN(r),IF(x=0,"",x)),
      a,JBA(lengths),
      o,PLSL(output),
      b,MATCH(s,a,0),
      t,IF(ISNUMBER(b),INDEX(replacements,b),""),
      u,IF(ISERROR(b),"",s),
      v,IF(u="",p,t),
      IF(o,VSTACK(v,IFERROR(b,0)),v)
   )
)

PLSL (stands for "parameter limit, single logical")
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not logically evaluable or more than a single entry in array brackets "{}"]))
Excel Formula: 
=LAMBDA([parameter],
   IF(
      IO(parameter),
      0,
      IF(
         JAN(parameter),
         NA(),
         LET(
            p,INDEX(parameter,1),
            IF(ISERROR(p),NA(),T1F0(p))
         )
      )
   )
)

The code for the remaining modules is not changed, so I'm not including them again here.
 
Additional more complex examples for multiple elements within multiple categories of replacements, while also demonstrating the use of the "priorities" feature of XREPLACE:

XREPLACE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2#CALC!basic types
3types1dt:NUMBER
423bookTRUE#DIV/0!note12411623122dt:TEXT
5FALSE 56.8 23#N/A423313211673dt:BLANK
60chair67notebook#REF!door121216424dt:LOGICAL
7107bookTRUE7.534131241116dt:ERROR
8#VALUE!100 book#DIV/0!163311332162
9advanced types
101dt:NUMBER
11multiples of multiple categoriesFALSEWWWWW#DIV/0!#DIV/0!ABCDE123456FALSEWWWWW#DIV/0!#DIV/0!ABCDE123456FALSEWWWWW#DIV/0!#DIV/0!ABCDE123456FALSEWWWWW#DIV/0!TRUEABCDEHELLO2dt:TEXT
12123456ABCDE123456ABCDEFALSE123456123456ABCDE123456ABCDEFALSE123456123456ABCDE123456ABCDEFALSE123456123456ABCDE123456ABCDEFALSETRUE31dt:BLANK_REAL
13123456123456123456123456123456123456123456123456123456123456123456123456123456123456123456123456123456123456123456HELLO123456HELLOTRUEHELLO32dt:BLANK_FORMULA
14ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!12345633dt:BLANK_SPACE
15123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!TRUEABCDE123456ABCDEWWWWWTRUE34dt:BLANK_PASTED
1641dt:LOGICAL_TRUE
17FALSEWWWWW#DIV/0!TRUEABCDEHELLOFALSEWWWWW#DIV/0!#DIV/0!ABCDE888888FALSEWWWWW#DIV/0!TRUEABCDE888888#DIV/0!WWWWW#DIV/0!#DIV/0!ABCDE12345642dt:LOGICAL_FALSE
18123456ABCDE123456ABCDEFALSETRUE123456ABCDE123456ABCDEFALSE123456123456ABCDE123456ABCDEFALSETRUE123456ABCDE123456ABCDE#DIV/0!123456161dt:ERROR_NULL!
19123456HELLO123456HELLOTRUEHELLO123456123456123456YYYYYY123456888888123456HELLO123456YYYYYYTRUE888888123456123456123456123456123456123456162dt:ERROR_DIV/0!
20ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEWWWWW#DIV/0!123456163dt:ERROR_VALUE!
21TRUEABCDE123456ABCDEWWWWWTRUE123456ABCDE123456ABCDEWWWWW#DIV/0!TRUEABCDE123456ABCDEWWWWWTRUE123456ABCDE123456ABCDEWWWWW#DIV/0!164dt:ERROR_REF!
22165dt:ERROR_NAME?
23#DIV/0!WWWWW#DIV/0!#DIV/0!ABCDE123456#DIV/0!HELLO#DIV/0!TRUEABCDEHELLOFALSE888888#DIV/0!TRUEABCDE888888#DIV/0!888888#DIV/0!#DIV/0!ABCDE888888166dt:ERROR_NUM!
24123456ABCDE123456ABCDE#DIV/0!123456123456ABCDE123456ABCDE#DIV/0!TRUE123456ABCDE123456ABCDEFALSETRUE123456ABCDE123456ABCDE#DIV/0!123456167dt:ERROR_N/A
25123456123456123456123456123456123456123456HELLO123456HELLOTRUEHELLO123456HELLO123456YYYYYYTRUE888888123456123456123456YYYYYY123456888888168dt:ERROR_GETTING_DATA
26ABCDE123456ABCDEWWWWW#DIV/0!123456ABCDE123456ABCDEHELLO#DIV/0!123456ABCDE123456ABCDE888888#DIV/0!123456ABCDE123456ABCDE888888#DIV/0!123456169dt:ERROR_SPILL!
27123456ABCDE123456ABCDEWWWWW#DIV/0!TRUEABCDE123456ABCDEHELLOTRUETRUEABCDE123456ABCDE888888TRUE123456ABCDE123456ABCDE888888#DIV/0!1610dt:ERROR_CONNECT!
281611dt:ERROR_BLOCKED!
291612dt:ERROR_UNKNOWN!
301613dt:ERROR_FIELD!
311614dt:ERROR_CALC!
321619dt:ERROR_PYTHON!
33
XREPLACE-3
Cell Formulas
RangeFormula
B2B2=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:N8I4=XTYPE(B4:G8,3)
E4,G8E4=1/0
E5E5=IF(1,"")
G5G5=#N/A
B11:G15B11=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"})
I11:N15I11=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,2,3,4})
P11:U15P11=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,2,4,3})
W11:AB15W11=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,3,2,4})
B17:G21B17=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,3,4,2})
I17:N21I17=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,4,2,3})
P17:U21P17=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{1,4,3,2})
W17:AB21W17=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{2,1,3,4})
B23:G27B23=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{2,1,4,3})
I23:N27I23=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{3,2,1,4})
P23:U27P23=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{4,3,1,2})
W23:AB27W23=XREPLACE(B4:G8,{23,"book"},{2,1},{3,16,2},{4,8},{FALSE,"WWWWW",#DIV/0!,123456,"ABCDE",TRUE,"HELLO",888888,"YYYYYY"},{4,2,3,1})
Dynamic array formulas.
 
Updated code: Massively improves the speed of the "by-count" core, i.e. JAV, of the function for large selections of data. The original core was MAP-based which was causing the slow-down. The new core is FREQUENCY based. (I initially tried MMULT as a substitute for MAP, but eventually noticed that FREQUENCY is even faster and more resource-efficient.)

JAV (by-count replacement core for XREPLACE)
Excel Formula: 
=LAMBDA(reference,counts,replacements,[output],
   LET(
      r,reference,
      d,JBA(counts),
      o,PLSL(output),
      s,UNIQUE(TOCOL(r)),
      t,IF(
         ISERROR(r),
         MATCH(ERROR.TYPE(r),ERROR.TYPE(s),0),
         MATCH(r,s,0)
      ),
      u,UNIQUE(TOCOL(t)),
      v,FREQUENCY(t,u),
      w,IF(
         ISNUMBER(MATCH(t,u,0)),
         INDEX(v,MATCH(t,u,0))
      ),
      x,IF(ISERROR(MATCH(w,d,0)),"",w),
      y,IF(
         ISNUMBER(MATCH(x,d,0)),
         INDEX(replacements,MATCH(x,d,0)),
         x
      ),
      z,IF(y="",r,y),
      IF(o,VSTACK(z,IF(x="",0,x)),z)
   )
)
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top