On Nov 8, 8:12*am, Mr Molio wrote:
Looking for a way, formula or macro, to search thru a cell and find
user account numbers within the text string of the cell. Once found, I
want to replace them with x's. This will basically be a 'scrub'
function to mask account numbers from view but keep the rest of the
cell's content intact. Here are some parameters:

1) each account number is 10 digits long, all numeric characters
2) ANY string within a cell that is 10 consecutive numeric characters
WILL be an account number
3) Account numbers are in no particular position within the cell. The
contents of the cell come from a chat log with customers, and they may
have typed the account number at any point in the string.

Once found, in each cell, I'd like to simply replace the numeric
characters with x's.

Any help is appreciated!

C

Try this small UDF:

Function IDKiller(s As String) As String
Dim L As Long, i As Long
Dim J As Long, JJ As Long
L = Len(s)
ReDim ary(1 To L)
For i = 1 To L
ary(i) = Mid(s, i, 1)
Next
kount = 0
J = 0
JJ = 0
For i = 1 To L
If ary(i) Like "#" Then
kount = kount + 1
If J = 0 Then J = i
JJ = i
If kount = 10 Then Exit For
Else
kount = 0
J = 0
JJ = 0
End If
Next

If kount = 10 Then
For jjj = J To JJ
ary(jjj) = "x"
Next
End If
IDKiller = ""
For i = 1 To L
IDKiller = IDKiller & ary(i)
Next
End Function


So if A1 contained:
dh11aks4jdhsjh1234567890675
=IDKiller(A1) will return:
dh11aks4jdhsjhxxxxxxxxxx675



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs