Remove spaces between numbers in a cell

removing spaces

Removing spaces from a string is quite simple. you can do the following

StringName = Replace(StringName, " ", "")

As for removing carriage returns, I am unsure.

Hi jefsboys,

If you wanted to do it with forumla you could use,

=SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(10),"")

Assuming your original cell is A1

Or with code

Sub x()
    Dim rngTemp As Range
    Dim strTemp As String
    For Each rngTemp In Range("A1:A10")
        strTemp = Replace(rngTemp.Value, " ", "")
        With rngTemp.Offset(0, 1) ' output in column B
            .NumberFormat = "@"
            .Value = Replace(strTemp, Chr(10), "")
        End With
    Next
End Sub

If it really is a carriage return then replace the value in the CHAR()/CHR() function with 13.

This should take care of it...

Notice that instead of removing characters, I'm creating a filter to only allow numbers and commas to flow thru. There's also a trick in there to overcome the problem with Excel wanting to interpret the string result as a number.

Sub CleanUp()
    For Each cell In ActiveWindow.RangeSelection
        With cell
            oldfmt = .NumberFormat
            n$ = .Value
            x$ = ""
            For i = 1 To Len(n$)
                char$ = Mid(n$, i, 1)
                Select Case char$
                    Case 0 To 9, ","
                        x$ = x$ & char$
                End Select
            Next i
            .NumberFormat = "@"
            .Value = x$
            .NumberFormat = oldfmt
        End With
    Next cell
End Sub

I offset the output so you could see the result. No problem over writing directly.

Arron's code worked for me. You do have to select the data you want processed before running it though.

The save as csv will put quotes around the data because it contains comma's
To stop the quotes you can either write your own export routine.

Sub ExportCSV()
    Dim rngTemp As Range
    Dim intUnit As Integer
    
    intUnit = FreeFile
    Open "X.csv" For Output As intUnit
    
    For Each rngTemp In Range("A1:A10")
        Print #intUnit, rngTemp
    Next
    Close intUnit
    
End Sub

Or use the 'text to columns' process first before saving as csv

jefsboys,

In case you need to do the clean-up without VBA, you could use the following formula:

=SUBSTITUTE(SUBSTITUTE(CLEAN(A1),"""","")," ","")

The outer SUBSTITUTE function gets rid of the Spaces, the inner one gets rid of the Quote Marks, and the CLEAN function removes the carriage returns.

Alternatively, the above formula can be used in your code with the help of the Application.worksheetfunction command.

Regards,

m

Quote from jefsboys

Excellent!
Would Aaron's code be the preferred snippet of code to remove the carriage returns and do the cleanup due to being more thorough?

Probably just a matter of personal preference.

I prefer to define characters for inclusion. This way when the unforeseen odd character occurs my code will still do its job. Also, I like the "Select Case" setup because I can span multiple characters with the "To" feature. Also notice I don't need to spend time looking up the character codes for all those oddball characters I want to exclude.

For instance, it's easy to change the filter from this...

Case 0 To 9, ","

To something like this to pickup the alphas and a few other special characters.

Case "A" to "Z", "a" to "z", 0 To 9, ",", "!", "?"

Use Replace function

Here is a simpler way to do this,

Just select the data, and say Ctrl+H ( For value replacement)

In find what? give a space by clicking spacebar,

In replace with- dont give anything and Click"Replace All",

the spaces will be removed,

hope it works,

Rajeev