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

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

> 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
>

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.

"Mike H" wrote:

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

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" <(E-Mail Removed)> wrote in message
news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
>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
>

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

> 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.
>
> "Mike H" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >

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" <(E-Mail Removed)> wrote in message
> news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
> >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
> >
>
>

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

"tonyd" wrote:

> 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" <(E-Mail Removed)> wrote in message
> > news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
> > >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
> > >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:AE728C1B-C5ED-49EA-A7D2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
>> >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
>> >
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:AE728C1B-C5ED-49EA-A7D2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
> >> >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
> >> >
> >>
> >>
>
>

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 (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:AE728C1B-C5ED-49EA-A7D2-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:6CECB4F9-2636-4501-9AD9-(E-Mail Removed)...
>>> >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
>>> >
>>>
>>>
>