1234 Main ST to 12XX Main St

[michiebee]

Hello,

I have address data that I need to edit for privacy. For example: 1234 Main St needs to be 12XX Main St. I also have addresses with <4 digits (300 High St=3XX High St & 95 Jones Blvd=9X Jones Blvd). How do I do this?

Thank you in advance for your help!!

[FDibbins]

Hi, welcome to the forumn

Maybe this?

	A	B
1	1234 Main St	12XX Main St
2	300 High St	3XX High St
3	95 Jones Blvd	9XX Jones Blvd

B1=LEFT(A1,IF(SEARCH(" ",A1)>=5,2,1))&"XX"&MID(A1,SEARCH(" ",A1),99)
copied down

[BadlySpelledBuoy]

A slight variation on Ford's formula.
It does almost the same thing, except it will 95 into 9X rather than 9XX. (Yes, just me being pedantic! )

Please Login or Register  to view this content.

BSB

[michiebee]

THANK YOU THANK YOU THANK YOU!! So glad I found this resource

[jason.b75]

Neither of those suggestions will censor a single digit, but this one will.

=REPLACE(A1,1,FIND(" ",A1)-1,LEFT(LEFT(A1,(FIND(" ",A1)-1)/2)&"XX",FIND(" ",A1)-1))

Will also work with more than 4 digits by increasing the number of X's in the formula.

[FDibbins]

Happy to help, thanks for the feedback

@ Jason, nicely done

[macropod]

Of course, if you don't replace all the strings with an output string that is the same length regardless of the number of digits in the address, you're not gaining much in privacy. That said, if you're outputting the street name, in most cases hiding/obfuscating part or all of the house number doesn't provide much privacy anyway. The process merely provides the illusion of privacy.

[jason.b75]

in most cases hiding/obfuscating part or all of the house number doesn't provide much privacy anyway.

I was thinking similarly, but the OP appeared to know what they needed. I would have thought that if this was to meet any kind of data protection requirement that there would be some guidelines to say what is or isn't acceptable.

Remembering that this is being done with a formula, the original, uncensored address string will still be clearly visible, either in an adjacent cell, with formula evaluation, or simply by editing the formula, there is no actual privacy at all. If anything, I would guess that this could be just for demonstration purposes, maybe a schoolteacher doing a classroom demonstration, but it's too early in the day for mind reading, so that's purely speculation.

Not everything requires the same level of security, if it did, hardware stores wouldn't carry such a wide variety of locks.