Here is a macro

Sub MoveData()
Dim i As Long
Dim cell As Range

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Value = Replace(Cells(i, "A").Value, " ", "|")
Next i
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
consecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1),
_
Array(3, 1), Array(4, 1), Array(5, 1))

For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell.Value)
Next cell

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Erik K via OfficeKB.com" <u13156@uwe wrote in message
news:56d9a5469aacb@uwe...
Yes, if there is only one space I do not want to move it to another cell
nor
delete the space. Any suggestions?

Bob Phillips wrote:
Does the question mean that if there is just one space you don't want to
split it? If not, you could use DataText To Columns delimited by space.

Can someone please tell me how to set a certain amount of spaces
between text
in a given cell? Here is what I have:
[quoted text clipped - 18 lines]
whatever text is set more than 2 spaces and move it to the next cell?
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1