Avatar of Svgmassive
Svgmassive
 asked on

Replace character

I am trying to clean up some imported data that has numerous special characters eg "-","/","\"," ").i would like to replace the  special characters with ","(chr(44)).I need the most efficient way of doing this.Thanks
Microsoft ExcelMicrosoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment
als315

8/22/2022 - Mon
Dale Fye

Where is your data coming from?  

What application do you want to do the "cleanup" from (Access or Excel)?

Is this for a single field in the data, or multiple fields?

Could some of these characters ("\") be delimiters for file names or in hyperlinks that should not be removed?
Svgmassive

ASKER
the application is ms access,they are not delimiters and the characters can be removed.Thanks
Dale Fye

one or more fields?

You can create a simple Update query, something like:

UPDATE yourTable
SET yourField = Replace([yourField], "/", chr$(44))

If you need to do this for multiple fields, and multiple characters, you could create an array of the characters and a second array of field names, then create two loops (one for fields, the other for characters) and run this query inside the inner loop.

This will not work if your table is a Linked Excel worksheet, it will only work if the data has been imported into Access.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Martin Liss

MyData = Replace(MyData, "/",chr(44))

Do the above for each special character.
ASKER CERTIFIED SOLUTION
als315

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.