How to anonymise a text?

Here is a function that obfuscates a range of cells. To use, set SourceArray (the first parameter in the first LET function) to the range of cells to obfuscate. The function returns an array of obfuscated values the same size as the source array. Alpha characters are converted to random alpha characters, numeric characters are converted to random numeric characters, and all punctuation characters are left untouched. Alpha character casing is retained. If used in a table, SourceArray must a single cell or a #SPILL! error will occur.

=LET(

SourceArray, B2:B4,

UpperVowels, "AEIOU",

LowerVowels, LOWER(UpperVowels),

UpperConsonants, "BCDFGHJKLMNPQRSTVWXYZ",

LowerConsonants, LOWER(UpperConsonants),

Numbers, "0123456789",

Result, MAKEARRAY(ROWS(SourceArray), 1, LAMBDA(Row,Column,

LET( 

  SourceValue, INDEX(SourceArray, Row), 

  TEXTJOIN( 

    "", 

    FALSE, 

    MAKEARRAY(1, LEN(SourceValue), LAMBDA(Row,Column, 

      LET( 

        Character, MID(SourceValue, Column, 1), 

        SWITCH(TRUE, 

          NOT(ISERROR(FIND(Character, UpperVowels))), MID(UpperVowels, RANDBETWEEN(1, LEN(UpperVowels)), 1), 

          NOT(ISERROR(FIND(Character, LowerVowels))), MID(LowerVowels, RANDBETWEEN(1, LEN(LowerVowels)), 1), 

          NOT(ISERROR(FIND(Character, UpperConsonants))), MID(UpperConsonants, RANDBETWEEN(1, LEN(UpperConsonants)), 1), 

          NOT(ISERROR(FIND(Character, LowerConsonants))), MID(LowerConsonants, RANDBETWEEN(1, LEN(LowerConsonants)), 1), 

          NOT(ISERROR(FIND(Character, Numbers))), MID(Numbers, RANDBETWEEN(1, LEN(Numbers)), 1), 

          Character 

        ) 

      ) 

    )) 

  ) 

) 

)),

IFERROR(VALUE(Result), Result)

)

An example:

Kevin

Here is a version that handles dates:

=LET(

SourceArray, B2:B6,

UpperVowels, "AEIOU",

LowerVowels, LOWER(UpperVowels),

UpperConsonants, "BCDFGHJKLMNPQRSTVWXYZ",

LowerConsonants, LOWER(UpperConsonants),

Numbers, "0123456789",

Result, MAKEARRAY(ROWS(SourceArray), 1, LAMBDA(Row,Column,

LET( 

  SourceValue, INDEX(SourceArray, Row), 

  IF( 

    SourceValue = "", 

    "", 

    IF( 

      CELL("format", SourceValue) = "D4", 

      DATE(YEAR(SourceValue) + RANDBETWEEN(-10, 10), MONTH(SourceValue) + RANDBETWEEN(-12, 12), DAY(SourceValue) + RANDBETWEEN(-31, 31)), 

      TEXTJOIN( 

        "", 

        FALSE, 

        MAKEARRAY(1, LEN(SourceValue), LAMBDA(Row,Column, 

          LET( 

            Character, MID(SourceValue, Column, 1), 

            SWITCH(TRUE, 

              NOT(ISERROR(FIND(Character, UpperVowels))), MID(UpperVowels, RANDBETWEEN(1, LEN(UpperVowels)), 1), 

              NOT(ISERROR(FIND(Character, LowerVowels))), MID(LowerVowels, RANDBETWEEN(1, LEN(LowerVowels)), 1), 

              NOT(ISERROR(FIND(Character, UpperConsonants))), MID(UpperConsonants, RANDBETWEEN(1, LEN(UpperConsonants)), 1), 

              NOT(ISERROR(FIND(Character, LowerConsonants))), MID(LowerConsonants, RANDBETWEEN(1, LEN(LowerConsonants)), 1), 

              NOT(ISERROR(FIND(Character, Numbers))), MID(Numbers, RANDBETWEEN(1, LEN(Numbers)), 1), 

              Character 

            ) 

          ) 

        )) 

      ) 

    ) 

  ) 

) 

)),

IFERROR(VALUE(Result), Result)

)

Kevin

Hello Morris Rock1, Thanks for using Microsoft products and posting in the community.

About your need to anonymize private information in text;

I see that you have placed your question in the Excel board, so if this information is a lot of repetitive information, you can just use the Filter, Replace function to accomplish this.

• The filter and replace function is in the “Edit Area” at the beginning;
• Or you can also use formulas to accomplish this operation: =IF(ISNUMBER(SEARCH("John", A1)), "Person A", A1)

This will look for “John” in cell A1 and replace it with “Person A” if found. Please modify the keywords to suit your needs.

However, if your need is to find a lot of different keyword information, I understand the amount of work involved, but there may not be an effective way to do this at the moment. Thank you for your understanding.

Best Regards,Arthur - MSFT | Microsoft Community Support Specialist