Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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 (MVP - Excel) "Mike H" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting number strings
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Mike H" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a number value to strings | Excel Worksheet Functions | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
Formatting Odd Number Strings | Excel Discussion (Misc queries) | |||
splitting number strings in excel | Excel Worksheet Functions | |||
Number Formatting when joining two strings | Excel Programming |