Professor Excel

Comments 1

  1. BobS

    Henrik, thanks for posting this. I’ve used single-cell array formulae for many things, but i often run into limitations. For example, I’d like to use arrays for iterating through a series of SUBSTITUTE functions. My idea is that I should be able to provide an array of terms and an array of replacement terms and perform a search and replace for each term in a cell, without having to nest SUBSTITUTES. But i can’t get the substitute to honor the array. Do you have any ideas? I’ve tried using the “evaluate formula” but it only goes through the first set of elements in my array.

    I’ve used Ctrl/Shift/Enter in all cases.

    Function: =SUBSTITUTE(Word,FindArray,ReplArray)
    Word: décor

    FindArray: ={“á”,”é”,”í”,”ó”,”ú”,”ñ”,”Á”,”É”,”Í”,”Ó”,”Ú”,”Ñ”,”km/h”}
    ReplArray: ={“a”,”e”,”i”,”o”,”u”,”n”,”A”,”E”,”I”,”O”,”U”,”N”,”kph”}

Leave a comment

%d bloggers like this: