A macro is the best way to do this

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/25/2008 by Joel Warburg
'
Application.CutCopyMode = False
'
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow
data = Range("A" & RowCount)
If data < "" Then
'removes spaces
secondstr = Left(data, InStr(data, " ") - 1)
thirdstr = Mid(data, InStr(data, " ") + 1)
fourthstr = Mid(thirdstr, InStr(thirdstr, " ") + 1)
thirdstr = Left(thirdstr, InStr(thirdstr, " ") - 1)

'seperate around colon
reversestr = StrReverse(secondstr)
colon_pos = Len(secondstr) + 1 - InStr(reversestr, ":")
firststr = Left(secondstr, colon_pos - 1)
secondstr = Mid(secondstr, colon_pos + 1)

Range("A" & RowCount) = fourthstr
Range("A" & RowCount).Insert Shift:=xlToRight
Range("A" & RowCount) = thirdstr
Range("A" & RowCount).Insert Shift:=xlToRight
Range("A" & RowCount) = secondstr
Range("A" & RowCount).Insert Shift:=xlToRight
Range("A" & RowCount) = firststr
End If
Next RowCount
End Sub


"Paul" wrote:

I am using delimited text-to-columns in Excel 2003. I would like to
break up a single text column into two additional text columns based
on the 1st two occurances of the space delimiter (" ") within each
cell. All other spaces should be ignored. Finally, for what remains
fo the original column, I'd like to break it into an additional column
based on the last occurance of the colon delimiter (":"), with any
other colons ignored. Is there a way to do this?

Before I imported the text into Excel, I could have made the job
easier by replacing the 1st 2 spaces, and the last colon, in the text
into a special delimiting character which occurs nowhere else, such as
"@". It would be a big step backward to go back to the external text
file because I've already done a lot of grouping of rows into an
outline structure (it's a somewhat hefty file with many levels of
groupings throughout).

Perhaps I can preserve the outline structure by re-importing the text
into adjacent columns (after injecting the unique delimiting
character)....Hmmmm.....