Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replace character

Posted on 2012-12-28
5
Medium Priority
?
218 Views
Last Modified: 2013-01-21
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
0
Comment
Question by:Svgmassive
5 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38726982
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?
0
 

Author Comment

by:Svgmassive
ID: 38727079
the application is ms access,they are not delimiters and the characters can be removed.Thanks
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38727141
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.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38727553
MyData = Replace(MyData, "/",chr(44))

Do the above for each special character.
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38727792
If this operation should be done only once, you can open table and use standard find & replace (Find button on ribbon or Ctrl+F). If it should be done many time, use update query with this function:
Function rpl(A As String) As String
Dim Arr As Variant, i As Integer
rpl = A
Arr = Array("-", "/", "\", " ")
For i = 0 To UBound(Arr)
    rpl = Replace(rpl, Arr(i), Chr(44))
Next i
End Function

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question