Find and Replace

[mrb783]

Hello, I'm throwing together a database that will allow users to take data from a MS SharePoint survey, import that into an Excel spreadsheet and then make eye-friendly reports based on that data in Access 2003 (though I do have to use Excel 2007 to import the data...long story...regardless it works for my needs thus far). Anyway, I've been able to do the majority of this just fine, have my fancy reports and whatnot, but now need to clean up the data a bit.

What I need to do is clean up the Active Directory authenticated name IDs that come in. They are exported from Sharepoint in something similar to the following format:

Doe, John (J.E.);#83;#McFarlen, Suzie (S.L.);#182;#Lancaster, McBob (M.D.);#32

I can do these just fine manually within Excel, but since I am trying to do as much automation as possible, it would be great if I can do the updates within Access and only have to run a single Macro/Query/Form to push a button, etc prior to running a report (for ongoing simplicity).

So, my question is: how do I perform inline updates to existing records without replacing the entire record? Namely, I want to replace the ";#83;#" and similar with "; " and the final ";#32" with "". The end result will be the following:

Doe, John (J.E.); McFarlen, Suzie (S.L.); Lancaster, McBob (M.D.)

Now, since these numbers change, I would obviously want to do something similar to how I would do this in Excel by using ";#???;#" to do my first set of replacements, then ";#??;#" for the second set, followed by ";#*" last. This should be simple, but I'm not sure how to go about building this macro or whatever it is I need to use. Any ideas? Thanks!

 

[HiTechCoach]

To get you started, Check out the VBA functions Replace(), Instr(), and MID(). There are the VBA functions I would use to do what you need.

 

[DCrake]

Unfortunately you cannot use wildcards using the Replace() function. A posible solution is it iterate through the string exmaining each character and splicing it up do remove the #'s and everyhing in between them.

A longwinded way would be to create a function and perform various replaces

Replace(String,"#","")
Replace(String,"1","")
Replace(String,"2","")
Replace(String,"3","")
etc.
Replace(String,"0","")