Replace Text Based on Variable Search

I have a db that has some older records that contain SSNs in the text.  I can 
serch the record, to find the records needing to be updated, but I don't know 
how to write the Replace function.

My search string is:  Like "*#########*" or Like "*###-##-####*"

Could someone help me with the code to update based on this criteria?

Thanks.

Sharkbyte
0
Utf
3/5/2010 7:17:02 PM
access 16762 articles. 2 followers. Follow

8 Replies
513 Views

Similar Articles

[PageSpeed] 35

On Fri, 5 Mar 2010 11:17:02 -0800, Sharkbyte <sharkbyte1@hotmail.com-> wrote:

>I have a db that has some older records that contain SSNs in the text.  I can 
>serch the record, to find the records needing to be updated, but I don't know 
>how to write the Replace function.
>
>My search string is:  Like "*#########*" or Like "*###-##-####*"
>
>Could someone help me with the code to update based on this criteria?
>
>Thanks.
>
>Sharkbyte

What do you want to replace, and with what? This is, I take it, a long text
(or Memo?) field containing a SSN embedded among other text? Do you want to
replace the SSN with another SSN, or with something else?

More info please!
-- 

             John W. Vinson [MVP]
0
John
3/5/2010 8:01:21 PM
Sorry John.  It doesn't seem to me that the code would be that much different 
whether I want to replace the number with x's (xxx-xx-xxxx) or simply with 
"".  I understand the Replace statement, I just can't figure the coding to 
capture the SSN, and process it.

I'm searching text and memo fields for SSNs, hence the Like statements.  I 
am looking to replace/remove - the SSN only - from the text.  

Thanks.



"John W. Vinson" wrote:

> On Fri, 5 Mar 2010 11:17:02 -0800, Sharkbyte <sharkbyte1@hotmail.com-> wrote:
> 
> >I have a db that has some older records that contain SSNs in the text.  I can 
> >serch the record, to find the records needing to be updated, but I don't know 
> >how to write the Replace function.
> >
> >My search string is:  Like "*#########*" or Like "*###-##-####*"
> >
> >Could someone help me with the code to update based on this criteria?
> >
> >Thanks.
> >
> >Sharkbyte
> 
> What do you want to replace, and with what? This is, I take it, a long text
> (or Memo?) field containing a SSN embedded among other text? Do you want to
> replace the SSN with another SSN, or with something else?
> 
> More info please!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/5/2010 9:08:58 PM
On Fri, 5 Mar 2010 13:08:58 -0800, Sharkbyte <sharkbyte1@hotmail.com-> wrote:

>Sorry John.  It doesn't seem to me that the code would be that much different 
>whether I want to replace the number with x's (xxx-xx-xxxx) or simply with 
>"".  I understand the Replace statement, I just can't figure the coding to 
>capture the SSN, and process it.
>
>I'm searching text and memo fields for SSNs, hence the Like statements.  I 
>am looking to replace/remove - the SSN only - from the text.  

That actually isn't trivial, since the Replace statement requires a literal
text string to be replaced. It doesn't accept wildcards. I suspect you'll need
to use custom VBA code to parse through the string finding sequences of
digits. 

This could be a real problem if there are OTHER digit strings in the code!

I'd suggest doing a search for "Regular Expressions", that's just what's
needed in this case. I've got some other work to do but I'll leave this thread
live and post back in a bit with some possible code to do it.
-- 

             John W. Vinson [MVP]
0
John
3/5/2010 10:04:40 PM
"John W. Vinson" wrote:

> I'd suggest doing a search for "Regular Expressions", that's just what's
> needed in this case. I've got some other work to do but I'll leave this thread
> live and post back in a bit with some possible code to do it.
> -- 


John:

What about the possibility, of when there is a match, capturing the value 
into a variable?  Then run the Replace command against that field, for the 
value stored in the variable.


0
Utf
3/6/2010 7:48:03 PM
On Sat, 6 Mar 2010 11:48:03 -0800, Sharkbyte <sharkbyte1@hotmail.com-> wrote:

>"John W. Vinson" wrote:
>
>> I'd suggest doing a search for "Regular Expressions", that's just what's
>> needed in this case. I've got some other work to do but I'll leave this thread
>> live and post back in a bit with some possible code to do it.
>> -- 
>
>
>John:
>
>What about the possibility, of when there is a match, capturing the value 
>into a variable?  Then run the Replace command against that field, for the 
>value stored in the variable.
>

I was trying to think how, but it's not obvious without using Regular
Expressions. You can't find the position of an imprecisely defined string
using InStr, for example. All I can suggest is VBA code which would step
through the string byte by byte looking for strings of numbers.
-- 

             John W. Vinson [MVP]
0
John
3/6/2010 9:18:03 PM
"John W. Vinson" wrote:

> On Sat, 6 Mar 2010 11:48:03 -0800, Sharkbyte <sharkbyte1@hotmail.com-> wrote:
> 
> >"John W. Vinson" wrote:
> >
> >> I'd suggest doing a search for "Regular Expressions", that's just what's
> >> needed in this case. I've got some other work to do but I'll leave this thread
> >> live and post back in a bit with some possible code to do it.
> >> -- 
> >
> >
> >John:
> >
> >What about the possibility, of when there is a match, capturing the value 
> >into a variable?  Then run the Replace command against that field, for the 
> >value stored in the variable.
> >
> 
> I was trying to think how, but it's not obvious without using Regular
> Expressions. You can't find the position of an imprecisely defined string
> using InStr, for example. All I can suggest is VBA code which would step
> through the string byte by byte looking for strings of numbers.
> -- 
> 
>              John W. Vinson [MVP]
> .

Can you offer some code suggestions on how to do this?  I could then run a 
generic search, to isolate the group of records, which include matches, then 
use your more specific code, to go into the particular record and search out 
the specific matches, and remove/replace the data.


0
Utf
3/8/2010 3:54:01 PM
"Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message 
news:07588B40-71D6-4C28-A8F5-2B11A396B6F2@microsoft.com...
>I have a db that has some older records that contain SSNs in the text.  I 
>can
> serch the record, to find the records needing to be updated, but I don't 
> know
> how to write the Replace function.
>
> My search string is:  Like "*#########*" or Like "*###-##-####*"
>
> Could someone help me with the code to update based on this criteria?
>
> Thanks.
>
> Sharkbyte 

0
De
3/13/2010 5:44:21 PM
jkjkjkjk

j"Sharkbyte" <sharkbyte1@hotmail.com-> a écrit dans le message de groupe de 
discussion : 07588B40-71D6-4C28-A8F5-2B11A396B6F2@microsoft.com...
> I have a db that has some older records that contain SSNs in the text.  I 
> can
> serch the record, to find the records needing to be updated, but I don't 
> know
> how to write the Replace function.
>
> My search string is:  Like "*#########*" or Like "*###-##-####*"
>
> Could someone help me with the code to update based on this criteria?
>
> Thanks.
>
> Sharkbyte 

0
joelgeraldine
3/17/2010 1:48:10 PM
Reply:

Similar Artilces:

Center text in text box
I produced our club's latest newsletter on Publisher for the first time and overall was a pretty pain free experience. Very intuitive program. I was unable to find a command to center text within a text box. Is this feature not present in Publisher as it is in word? It was not on the tool bar with the margin justifying commands and my quick search didn't bring it up. I ended up ball parking my lines in the center with tab and space bar. Any suggestions? Right-click the toolbar, tick "formatting". -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

Offset text print problems
recently all of my excel documents are printing the text offset from the gridlines. I have checked through every setting I can think of and am having no luck. I have even changed the font I am using. Any ideas on why this is happening and or how to fix it ? I have no idea, but I'd start with the printer driver. Either get a fresh or new driver from your printer's manufacturer. You may even want to try it on a different printer to see if that makes a difference. IMC Techie wrote: > > recently all of my excel documents are printing the text offset from the > gridlines....

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Filter query from text box
I have a query that is filtered out by a year. I have a form that the user enters in the year. In the query, if I just put in a year of 2009. It works fine. If I replace the filter criteria to this: [Forms]![frmSalesAnalysis]![Year] I get a ODBC-Failed error. Any ideas. Thanks, Jasper Post SQL of query by opening in design view, click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Jasper Recto" wrote: > I have a query that is filtered out by a year. > > I have a form that the ...

Extracting the number from a text
Hi, In a cell say C3, the value is such as S9(09)V(05). the value of that cell might be in one of the following format: (1) S(9)V(5) (2) S9(09)V(05) (3) S9(9)V(05) (4) S9(09)V(5) (5) S(11) My requirement is: i need to get the numbers extracted which, is present before & after the letter 'V' & enclosed in parantheses. In some cases, the letter V may not be there. (for ex: point 5 - here, only one number is there ie.11) This extraction should happen ONLY if the cell E3 has the value "COMP-3." Thanks in Advance. Venkatesh V First number: =IF($E$3="COMP...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

Access 2002/2003 VBA What kind of file is .CRC (Binary, text) ? How do I read it with FreeFile()?
Dear All Access 2002 w/SP3 Access 2003 w/SP2 Windows XP Pro w/SP2 Type of file: CRC File Opens with: Unknown application I want to read all the data in a .CRC file I know I can read it using FSO, if I click on File name -> right click on properties, Changes and choose the notepad to open it. But I do not want to do this, I want to open the file as is using FreeFile() and read all the data The file was created using a program called CdCheck http://www.kvipu.com/CDCheck/ Can someone please help? Thanks in advance Regards, Antonio Macias rebelscum0000 <piiixp@jedikeeper.com> wro...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Customized Text Fields
I am having a problem seeing the data from customized text fields in the Resource Usage view. For example I have a text field for charge numbers that are assigned to each task. I can view it in the Ghantt Chart. However, when I go to the Resource Usage view and insert that text column the data doesn't appear. -- DJW In article <2A53FDE3-F008-4134-838C-268B567209F8@microsoft.com>, DWilliams <DWilliams@discussions.microsoft.com> wrote: > I am having a problem seeing the data from customized text fields in the > Resource Usage view. For example I...

Text Overlayment
Is there a way to overlay on letter with another - so that both are visible? Or is there some way to create a composite? For examples: M overlaying W or P overlaying X or T overlaying O? WordArt? Create a text box on the Master page with your first letter, on the publication page create another text box with the second letter. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "PatJennings" <pfjennings@charter.net> wrote in message news:usKPm$xfGHA.356@TK2MSFTNGP02.phx.gbl... > Is there a way to overlay on l...

Kit: cost base for pricing coming from components not kit item.
Hello, I=92ve created a kit item from several component items. I then set a standard cost and current cost on the kit =96 different from the sum of the cost of the component items. In the pricing screen I selected a pricing method, entered a percentage and the resultant selling price showed the correct calculation based on the percentage markup and cost I set for the kit item. However in SOP on an order the selling price uses the correct markup percentage but the cost is based on the sum of the component items not the cost set for the kit. GPv10 no SPs installed. Is this a known bug? j ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

alternate quoted text formatting?
Is there any way to change Outlook's behaviour vis-a-vi quoted text in replies. It uses vertical bars to indicate quote level, but these just don't show up (in any consistent way) when viewed in any non-Outlook mail client. I'd much prefer Outlook to use ">" for quoted text or at least some method that is not limited to viewing in Microsoft clients. The only way I can find to do this is to use the view all mail as plain text option, but then if I'm forwarded an e-mail from an Outlook user where there is already quoted text in the vertical bars style, these are jus...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

AutoCorrect
In my AutoCorrect window next to Replace and With, why are the options plain text and formatted text greyed out? "Formatted text" is available only if you have selected formatted text before opening the dialog (or pasted in text that has characters that require formatting). "Formatted text" means any text that contains characters from more than one font or that has, say, italic or bold formatting applied. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Elizabeth" <Elizabeth@discus...

Is the text implying that FORMSOF(INFLECTIONAL...) will match either nouns or verbs, but not both?
hi 1) > A given <simple_term> within a <generation_term> will not match both noun= s and > verbs. If I understand the above text correctly, then query SELECT * FROM someTable WHERE CONTAINS ( *, ' FORMSOF ( INFLECTIONAL, park ) ' ) will search for either nouns or verbs derived from the root word =93park=94, but not for both? Thus out of the two rows, one containing noun =93parks=94 and other verb =93parking=94, the above query should retur= n just one of the two rows? But as it turns out, query returns both rows, so perhaps my assumptions are a ...

Global variable lost in Change event Handler?
BACKGROUND ---------- I have an ASP.NET application having two panels. In one panel, an XML document, transformed using xsl, is displayed. In the other panel are some controls that allow a user to change the xml. For example, each "l_item" element appears as a row in a table labelled with a "label"; so if I have <l_item id="1"> <label>blah</label> ... </l_item> it appears on the web page as ------------- BLAH: ... ------------- In the other panel there is a textbox control, where the user can type a new label and click a but...

Change text based on date
I want to change the text color on a report using the attached code but can't get it to wor not sure where I'm going wrong txtOrderDueDate is a long date? 12/08/2009 Private Sub Report_Load() If Me.txtOrderDueDate.Value <= Date + 2 Then Me.txtOrderDueDate.ForeColor = RGB(255, 0, 0) Else Me.txtOrderDueDate.ForeColor = RGB(0, 255, 0) End If End Sub Thanks! Jerry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1 jbair via AccessMonster.com wrote: >I want to change the text color on a report using t...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

There should be a shortcut icon for "wrap text"
There is an icon for almost every other formatting function, and wrap text is one I use continually, but there's no shortcut and it takes several steps each time. Terri You could put this code into your personal.xls and assign a shortcut key or custom toolbar button to it, trhis will make it one-press Sub WrapText() Dim myCell As Range For Each myCell In Selection myCell.WrapText = True Next myCell End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Terri@Lear" <Terri@Lear@discussions.microsoft.com> wrote in...