Go Premium for a chance to win a PS4. Enter to Win

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
?
90 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 53

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Gain an elementary understanding of Blockchain technology.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Integration Management Part 2

879 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