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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 25 Mar 2008 18:35 GMT

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

Reply to this Message

Paul - 25 Mar 2008 19:01 GMT

> 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
[quoted text clipped - 15 lines]
> into adjacent columns (after injecting the unique delimiting
> character)....Hmmmm.....

Bingo. Problem solved as described. Thanks anyway!

Reply to this Message

Joel - 25 Mar 2008 19:18 GMT

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

Reply to this Message

Paul - 26 Mar 2008 02:51 GMT

> A macro is the best way to do this
>
[quoted text clipped - 52 lines]
> > into adjacent columns (after injecting the unique delimiting
> > character)....Hmmmm.....

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.

Reply to this Message

Gord Dibben - 25 Mar 2008 19:25 GMT

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

>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
[quoted text clipped - 15 lines]
>into adjacent columns (after injecting the unique delimiting
>character)....Hmmmm.....

Reply to this Message

Paul - 26 Mar 2008 02:55 GMT

On Tue, 25 Mar 2008 10:35:20 -0700 (PDT), Paul
<Paul.Domas...@gmail.com> 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
[quoted text clipped - 16 lines]
> 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.

Reply to this Message