Is there shared best practice to randomize sensitive numeric data for Excel ?

Brass Contributor

220812 Is there shared best practice to randomize  sensitive numeric data for Excel .jpgHow to randomize sensitve numerical data in Excel while maintaining existing data format(s) for public post? This is to supply Technical Support all information (format, shape ) except confidential content. 

 

IMHO, there should be a RAND formula to execute for each numeric digit as follows; is there a simpler solution?

 

4 Replies

@sunnyschindler Try MID() with SEQUENCE() function.

=MID(B3,SEQUENCE(LEN(B3)),1)

 

Harun24HR_0-1660282569981.png

 

@sunnyschindler  so what is meant by "maintaining existing data formats" can be different for many applications but if I understand you correctly you want to maintain the length of the number but randomize the digits.  Does it really need to have random digits?  why not use this formula to display a sequence of 1's of the same length:

=REPT(1,LEN(A1))

 if you really want random numbers then this could work

=CONCAT(RANDARRAY(LEN(Q12),1,1,9,TRUE))

 I excluded 0 so you didn't have a number starting with 0, you could make do the first digit and then include 0 after the first digit

in any case these formulas will only display an output based on the value in another cell.  In other words, the original cell is still there.  If you want to obfuscate the file then you probably need a macro to overwrite the values in the sheet.

@mtarler 

 

Thanks for suggestion. Because PowerPoint compare do not cover text within image (Financial Spreadsheet with Arial Size 2.5) prompt me to try other ways such as Office Word OCR or Adobe Export to Spreadsheet. However, even Adobe Export is less than 100% accurate for Arial size 2.5.

 

To present this problem to Technical Supports who are unable to replicate my problem, I need to supply as much information as possible but maintain confidentialality. 

 

Without in-depth understanding what affects OCR accuracy, my guess is to provide worst combination of 6 digit length of financial numerics (used with Percentage and Minus Signs) but have not yet find documented of such combination; IMHO, I am assuming random numerics is better than your suggested series of numeric 1s...

Your further support will be much appreciated!

 

I'm not following the root issue here. Maybe you should ask the community about the main/root problem you are having. I don't understand what you are trying to do with powerpoint, word or adobe. It sounds like you are doing OCR but then I don't understand how excel comes into play.
w/r to the question about random digit replacement the second formula I gave will do that:
=CONCAT(RANDARRAY(LEN(Q12),1,1,9,TRUE))
and if you want the 0 s to be included then:
=RANDARRAY(1,1,1,9,TRUE) & CONCAT(RANDARRAY(LEN(Q12),1,0,9,TRUE))