Deleting number strings

[tonyd]

I have a column with a lot of information in it from our clients. some of
the cells have credit card numbers in them. all i want to do is tell excel
to find any group of 4 numbers ex. 5689 or 4589 and delete it. here is a
breif example of what you may find in any given cell. the numbers are not
real BTW. i have 4 thousand cells like the one below, i cant change them it
would take too long, i just want excel to find groups of four numbers and
then change them so no one can see the phone numbers, cc numbers or ss numbers
thank you





08-04-03
08-01-03 - I called him and He confirmed that he will take the intermediate
course
07-28-03 - He emailed to charge his account on monday
07-25-03 - he reffered his friend ***@hotmail.com
07-25-03 - registered for Intro course
Visa 5896 - 2115 - 1709 - 4589 Exp: 11-2004
07-23-03 - said he wants to take the course during the FS.
07-23-03 - attended FS
07-23-03 - Comfirmed to confirm free seminar

*****@hotmail.com

 

[Mike H]

Hi,

It won't get you an answer any quicker by posting in different groups, the
people here generally read all of them.

Why not try
Edit|Replace
enter 5896
and in the replace box enter **** or leave it blank
Click replace all

The credit card number will end up looking like

**** - 2115 - 1709 - 4589 Exp: 11-2004

Mike

 

[tonyd]

there are many credit card numbers....so one just wont work, anyway your
answer in here was better than the last...but i find it hard to beleive that
excel cant do the following steps

1-search cells for any number between 0000-9999
2-delete or replace it

call me crazy but it doesnt seem much i just dont know the answer though.

 

[Rick Rothstein]

For this entry (which is the one I presume we are supposed to concentrate
on)...

07-25-03 - registered for Intro course Visa 5896 - 2115 - 1709 - 4589 Exp:
11-2004

what did you want the output to look like? I ask because if all we do is
remove four digit number, your final output would be this...

07-25-03 - registered for Intro course Visa - - - Exp: 11-

Notice the four digit year from the Exp date was removed too.

 

[Mike H]

Excel can do that with a macro but we need to be clear on the data layout.
Is this all in one cell or multiple cells?

07-25-03 - registered for Intro course Visa 5896 - 2115 - 1709 - 4589 Exp:
11-2004

Mike

 

[tonyd]

that would be fine for the output. i just need them blanked out and the 4
thousand cells each have the ssame amount data in them that was below in my
first questiion

 

[Mike H]

Tony,

This assumes all of your data are in column A, It will replace any 4
consecutive numbers with ****
Right click you sheet tab, view code and paste this in and run it

Sub marine()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
For x = 1 To Len(c.Value)
testnumber = (Mid(c.Value, x, 4))
For y = 1 To 4
If IsNumeric(Mid(testnumber, y, 1)) Then
cr = cr + 1
End If
If cr = 4 Then
c.Value = Application.WorksheetFunction.Substitute(c.Value, testnumber,
"****")
End If
Next
cr = 0
Next
Next
End Sub

Mike

 

[Rick Rothstein]

This is interesting... I just developed a function for a question in one of
the compiled VB newsgroups that can be put to use on your question... and it
gives the flexibility meet your request in one of two ways. Here is that
function...

Function AmbiguousString(TextString As String, Pattern As String, _
Optional FindSmallest As Boolean = True) As String
Dim X As Long
For X = 1 To Len(TextString)
If Mid(TextString, X) Like Pattern & "*" Then
AmbiguousString = Mid(TextString, X)
Exit For
End If
Next
If Len(AmbiguousString) > 1 Then
If FindSmallest Then
For X = 1 To Len(AmbiguousString)
If Left(AmbiguousString, X) Like Pattern Then
AmbiguousString = Left(AmbiguousString, X)
Exit For
End If
Next
Else
For X = Len(AmbiguousString) - 1 To 1 Step -1
If Left(AmbiguousString, X) Like Pattern Then
AmbiguousString = Left(AmbiguousString, X)
Exit For
End If
Next
End If
End If
End Function

What it does is search a String value and return a substring that meets a
Like Operator type Pattern. For example, if you had this String value...

MyString = "One Two Three Four Five Six Seven Eight Nine Ten"

and you wanted the substring that started with the word Three and ended with
the word Seven, then you could get this with this function call...

MsgBox AmbiguousString(MyString, "Three*Seven")

The Pattern string can be any valid Like Operator expression.

Anyway, this function gives us the flexibility to construct at least one of
two macros that you can make use of. This first macro removes the entire
VISA number, but leaves the Exp. date as is...

Sub RemoveVisaNumber()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Const DataStartRow As Long = 2
Const DataColumn As String = "A"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To LastRow
CellValue = .Cells(X, DataColumn).Value
.Cells(X, DataColumn) = Replace(CellValue, AmbiguousString( _
CellValue, "####*####", False), "")
Next
End With
End Sub

This second macro removes all four-digit numbers no matter where they are...

Sub RemoveAllFourDigitNumbers()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Const DataStartRow As Long = 2
Const DataColumn As String = "A"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To LastRow
CellValue = .Cells(X, DataColumn).Value
Do While CellValue Like "*####*"
CellValue = Replace(CellValue, AmbiguousString( _
CellValue, "####", False), "")
Loop
.Cells(X, DataColumn).Value = CellValue
Next
End With
End Sub

Note that in both macros, you have to change my example worksheet name
(Sheet2) and the start row (2) and column ("A") for your data to whatever is
appropriate for your worksheet.

So, simply chose which of the two macros you want to use along with my
AmbiguousString function into a Module and you are good to go... just run
the macro.

 

[Mike H]

Hi Rick,

I like the removevisanumber, mine was a bit of a blunt instrument, it works
but not as fast or effecient as this. One for my macro archive

Mike

 

[Rick Rothstein]

By the way, I realize using my AmbiguousString function makes the code
longer, but I really like the flexibility available with this function, so I
wanted to make it available in case anyone else might find it useful too.
One thing I forgot to mention is the optional FindSmallest argument... it
controls whether to find the smallest substring that matches the pattern or
the largest one that matches the pattern. For example, let's say your
TextString is "abXcdeXfghXijk" and your Pattern is "X*X"... the smallest
substring to match that pattern is "XcdeX" and the largest is "XcdeXfghX".
The default is for the function to return the smallest matching substring.
Of course, if there is only one substring that matches the pattern, it will
be return for either setting.

 

[Rick Rothstein]

Well, of course, the key to that macro is the AmbiguousString string
function, which I think is a kind of neat, flexible function. True, you
could employ Regular Expressions which are far, far more powerful, but for
the bulk of find operations, Regular Expression tend to be over-kill
(especially if you are not all that familiar with its pattern syntax... the
Like operator's pattern syntax is a little easier to get one's head around).

 

[Rick Rothstein]

Oh, and while I'm sure you already realized it, I just wanted to say for the
archives that the function, if placed in a Module, can be used, as is, as a
User Defined Function (UDF) in your worksheet formulas.