[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I clean the data in a cell or special characters, spaces, and carriage returns?

Posted on 2015-01-31
2
Medium Priority
?
93 Views
Last Modified: 2016-02-10
I am trying to clean and normalize data. Cells have carriage returns in them (Alt+Enter), special characters (%^*)/!~), and blank non printing characters. I have come across a few different methods, but each is a separate operation.

Is there a way to select the cells in question, and have a macro perform it all?

The attached file has the three different processes that I have found so far. Two are subs and one is a function. I am hoping to combine them so that one macro can do all three exercises at once.

Thanks for having a look....
Remove-Returns-And-Special-Characters.xl
0
Comment
Question by:DougDodge
2 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40582390
Hi,

pls try

Sub RemoveAll()
Dim cel
Dim strTmp, strSpecialChars
Dim i

For Each cel In ActiveSheet.UsedRange
    strTmp = Trim(Application.Clean(Replace(cel, Chr(10), " ")))
    strSpecialChars = "\/:*?™""®<>|.&@#(_+`©~);-+=^$!,'" 'This is your list of characters to be removed
    
    For i = 1 To Len(strSpecialChars)
        strTmp = Replace$(strTmp, Mid$(strSpecialChars, i, 1), "") 'this will remove spaces
    Next
    strTmp = Replace$(strTmp, "  ", " ")
    cel.Value = strTmp
Next
    
End Sub

Open in new window

Regards
0
 

Author Comment

by:DougDodge
ID: 40583208
It worked, just the way I had hoped.....

Thanks....
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

591 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