Text-to-columns, but only 1st 2 & last delimiters

[Paul]

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.....

 

[Paul]

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.....

Bingo. Problem solved as described. Thanks anyway!

 

[Joel]

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

 

[Gord Dibben]

You do have the option of skipping certain fieds when doing data>text to
columns.

You will most likely have to make several trips though to get the configuation
you want.


Gord Dibben MS Excel MVP

 

[Paul]

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

Thanks, Joel. This will be a good way to get a trial-by-fire on VBA.
Next chance I'll have to indulge will be in 2 weeks time, but I'm glad
that your code is available for future scrutiny.

 

[Paul]

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.....

You do have the option of skipping certain fieds when doing
data>text to columns.

You will most likely have to make several trips though to get
the configuation you want.

Thanks, Gord. I've managed to finangle the text code with unique
delimiters where needed, and pull it into Excel using one pass
while still preserving the outline structure. I may have future
reason yet revisit this ability to skip certain fields upon
importing.