line breaks & trimming

I'd like to insert a line break into or replace a certain amount of spaces. I've tried using find & replace and did a couple searches that referenced char(10) as line break but it didn't work. I also tried finding char(9) for tabs but I doubt they make it through excels text import process.

q.1.
I need a macro for replacing for every 10 blank spaces and put a line break in the cells of a whole column or selection in excel?


---====---=-=-=-=-=-=-=----AND


I found this vba for removing spaces in a cell
Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub

q.2.
How can I make it trim for a whole column or selection?


Yes I am new to all this and fiddled a bit to actually get the vb editor up, I appreciate the help.

chr(10) is line feed and chr(13) is carrage return. How they are used depends on where the text comes from. You might see chr(13) only, chr(10) onlt or both used in combination. Might be a good idea for you to make a small test sub that prints then ascii numbres for you so taht you can see what you are dealing with.

If you want to apply code to a whole section you'll need to loop through each cell. The code for just looping though the cells are easier, but reading them into an array first is quicker.

-Terry

Public Sub ProcessData()
Const TEST_COLUMN As String = "J" '<=== change to suit
Const TEST_VALUE = " " '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

.Cells(i, TEST_COLUMN).Value2 = Replace(.Cells(i, TEST_COLUMN).Value2, TEST_VALUE, Chr(10))
Next i
End With

Application.ScreenUpdating = True

End Sub