Clean spaces and returns when leaving a cell

[rjplante]

I have a single 10 character block of text that is copied from another location and pasted into cell B2. It almost always contains leading and trailing spaces as well as a carriage return. I would like to have the user paste the value into the cell and when the hit tab, or the enter key to go to the next cell, the macro will automatically run to clean up the text. How do I get this to work. I have the following code in the individual worksheets code window. When I select the cell and tab/enter to the next cell, nothing happens.

I don't know what to do to get this to run and the cell selection is changed to cell B4.

Thanks for the help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim snData As String
Dim newData As String


snData = Range("B2").Value


newData = Replace(Replace(snData, vbCrLf, ""), " ", "")


End Sub

 

[healey21]

Try using the Trim function

Range("B2") = Trim("B2")

 

[rjplante]

Awesome, Thanks! Just had to change it to Range("B2") = Trim(Range("B2").value) and it all worked great.

 

[healey21]

Pleased to read you have a solution.