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" wrote:

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.

--
Rick (MVP - Excel)


"tonyd" wrote in message
...
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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"tonyd" wrote in message
...
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
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