Hi

Could anyone offer any help on how to split the following into
seperate columns....
All data is in column A, and is formatted as: -

Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Industries Today at 10:27:32am
Parsnips Buy The Farming Corp. Today at 11:23:57am
Parsnips Sell The Superstore Confed. Today at 12:05:09pm

I would like the text split into columns as: -

Column B Col C Col D Col E Col F ColG
Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Products Today at 10:27:32am
etc

The text needs to be split around the common words of 'buy', 'sell',
'at' and 'today'. These words will always appear in the original
format.


Thanks for any help

You need to use the find, left, right, length and mid formulas.

The formulas will be a little bit ugly. Here is one of the tougher one...

if (Find("Buy", "A1")0, mid("A1", Find("Buy", "A1") + 3, Find("at", "A1"),
mid("A1", Find("Sell", "A1") + 4, Find("at", "A1"))

Parsing text is a little tricky and takes some playing around but this
should give you a start.

"Jon Atkins" wrote:

Hi

Could anyone offer any help on how to split the following into
seperate columns....
All data is in column A, and is formatted as: -

Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Industries Today at 10:27:32am
Parsnips Buy The Farming Corp. Today at 11:23:57am
Parsnips Sell The Superstore Confed. Today at 12:05:09pm

I would like the text split into columns as: -

Column B Col C Col D Col E Col F ColG
Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Products Today at 10:27:32am
etc

The text needs to be split around the common words of 'buy', 'sell',
'at' and 'today'. These words will always appear in the original
format.


Thanks for any help

Jon


the "neatest" thing i can come up with involves a named formula.
(this is needed else you cannot call the macrofunctions)


insert a name formula

Name : Splitter
Refersto:

=EVALUATE("={"""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(Sheet1!$A1,
" Buy ","|Buy|")," Sell ","|Sell|")," Today at ","|Today at|"),"|",CHAR
(34)&INDEX(GET.WORKSPACE(37),14)&CHAR(34))&"""}")


now
select c1:f1
type =splitter
close with ctrl shift enter.

copy down the array formula as far as you need.

hmm... this IS cool

have fun coding!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Jon Atkins wrote:

Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Industries Today at 10:27:32am
Parsnips Buy The Farming Corp. Today at 11:23:57am
Parsnips Sell The Superstore Confed. Today at 12:05:09pm

If I were doing this manually, I'd try to do Data|text to columns.

But I'd need a delimiter. I'd do some editing (against a copy of the
worksheet???)

First, change "buy" to "|Buy|"
then change "today at" to "|today|at|"
and then run data|text to columns (delimited by the vertical bar)
and I'd change the data (time) in column G to a real time.
and finally, change column A back (or delete it completely???)

As a macro, it would look something like:

Option Explicit
Sub testme01()

Dim myStrings As Variant
Dim myReplacements As Variant
Dim iCtr As Long
Dim wks As Worksheet

myStrings = Array(" Buy ", " Sell ", " Today at ")
myReplacements = Array("|Buy|", "|Sell|", "|Today|at|")

If UBound(myStrings) < UBound(myReplacements) Then
MsgBox "not the same number of elements!"
Exit Sub
End If

Set wks = Worksheets("sheet1")
With wks
With .Range("a:a")
For iCtr = LBound(myStrings) To UBound(myStrings)
.Replace what:=myStrings(iCtr), _
Replacement:=myReplacements(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

.TextToColumns Destination:=.Cells(1).Offset(0, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
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))
End With

'Change column A back
With .Range("a:a")
.Replace what:="|", _
Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End With

'fix the time in G
With .Range("G:G")
.Replace what:="am", _
Replacement:=" AM", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="PM", _
Replacement:=" PM", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.NumberFormat = "hh:mm:ss"
End With

.UsedRange.Columns.AutoFit
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Jon Atkins wrote:

Hi

Could anyone offer any help on how to split the following into
seperate columns....
All data is in column A, and is formatted as: -

Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Industries Today at 10:27:32am
Parsnips Buy The Farming Corp. Today at 11:23:57am
Parsnips Sell The Superstore Confed. Today at 12:05:09pm

I would like the text split into columns as: -

Column B Col C Col D Col E Col F ColG
Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Products Today at 10:27:32am
etc

The text needs to be split around the common words of 'buy', 'sell',
'at' and 'today'. These words will always appear in the original
format.

Thanks for any help

--

Dave Peterson

You might want to add that if you copy a cell with this formula and paste it
to another worksheet, you will get a general protection fault and your
workbook will close losing all unsaved changes. this is in xl2000 and
earlier. I don't believe you get the failure in xl2002 and later.

--
Regards,
Tom Ogilvy



"keepITcool" wrote in message
...
Jon


the "neatest" thing i can come up with involves a named formula.
(this is needed else you cannot call the macrofunctions)


insert a name formula

Name : Splitter
Refersto:

=EVALUATE("={"""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(Sheet1!$A1,
" Buy ","|Buy|")," Sell ","|Sell|")," Today at ","|Today at|"),"|",CHAR
(34)&INDEX(GET.WORKSPACE(37),14)&CHAR(34))&"""}")


now
select c1:f1
type =splitter
close with ctrl shift enter.

copy down the array formula as far as you need.

hmm... this IS cool

have fun coding!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Jon Atkins wrote:

Baked Beans Buy Zeinz Products Today at 10:03:42am
Baked Beans Sell Maru Industries Today at 10:27:32am
Parsnips Buy The Farming Corp. Today at 11:23:57am
Parsnips Sell The Superstore Confed. Today at 12:05:09pm

Tom...

such a cool solution and you start "splitting hairs"

<grin

a: most likely is that after inserting the named formula the "split
values" are copied/pasted as values see 2nd code below :)


b:
Tried it:
it doesn't crash xl97 (SR-2 Dutch)..
it doesn't crash xlXP
it doens't crash xl2003
unfortunately I cant test xl2000

Could the crash be related to security setting not allowing xl4 macro's?
Or could it be that with testing the Name "Splitter" was ghosted and not
fully removed via set.name("splitter","")



For other's that want to test:

Sub DoesThisCrashInExcel2000()

Workbooks.Add xlWBATWorksheet


ActiveWorkbook.Names.Add Name:="splitter", RefersToR1C1:= _
"=EVALUATE(""={""""""&SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE
(RC1,"" Buy "",""|Buy|""),"" Sell "",""|Sell|""),"" Today at
"",""|Today|At|""),""|"",CHAR(34)&INDEX(GET.WORKSP ACE(37),14)&CHAR(34))
&""""""}"")"

Range("A1:a10") = "Baked Beans Buy Zeinz Products Today at 10:03:42am"
With Range("B1:E10")
.Rows(1).FormulaArray = "=splitter"
.FillDown
.Copy
End With

Workbooks.Add xlWBATWorksheet
Cells(1, 2).Activate
ActiveSheet.Paste

End Sub

Sub FasterThanLooping()

Workbooks.Add xlWBATWorksheet
Range("A1:A10000") = "Baked Beans Buy Zeinz Products Today at 10:03:42am"

ActiveWorkbook.Names.Add Name:="splitter", RefersToR1C1:= _
"=EVALUATE(""={""""""&SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(RC1,""
Buy "",""|Buy|""),"" Sell "",""|Sell|""),"" Today at
"",""|Today|At|""),""|"",CHAR(34)&INDEX(GET.WORKSP ACE(37),14)&CHAR(34))
&""""""}"")"

With Range("B1:F10000")
.Rows(1).FormulaArray = "=splitter"
.FillDown
.Value=.value
End With

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

You might want to add that if you copy a cell with this formula and
paste it to another worksheet, you will get a general protection fault
and your workbook will close losing all unsaved changes. this is in
xl2000 and earlier. I don't believe you get the failure in xl2002 and
later.

It crashed xl97, US English and this is a well established problem using
xl4Macros in formula.

http://groups.google.com/groups?as_q...20& lr=&hl=en

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...
Tom...

such a cool solution and you start "splitting hairs"

<grin

a: most likely is that after inserting the named formula the "split
values" are copied/pasted as values see 2nd code below :)


b:
Tried it:
it doesn't crash xl97 (SR-2 Dutch)..
it doesn't crash xlXP
it doens't crash xl2003
unfortunately I cant test xl2000

Could the crash be related to security setting not allowing xl4 macro's?
Or could it be that with testing the Name "Splitter" was ghosted and not
fully removed via set.name("splitter","")



For other's that want to test:

Sub DoesThisCrashInExcel2000()

Workbooks.Add xlWBATWorksheet


ActiveWorkbook.Names.Add Name:="splitter", RefersToR1C1:= _
"=EVALUATE(""={""""""&SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE
(RC1,"" Buy "",""|Buy|""),"" Sell "",""|Sell|""),"" Today at
"",""|Today|At|""),""|"",CHAR(34)&INDEX(GET.WORKSP ACE(37),14)&CHAR(34))
&""""""}"")"

Range("A1:a10") = "Baked Beans Buy Zeinz Products Today at 10:03:42am"
With Range("B1:E10")
.Rows(1).FormulaArray = "=splitter"
.FillDown
.Copy
End With

Workbooks.Add xlWBATWorksheet
Cells(1, 2).Activate
ActiveSheet.Paste

End Sub

Sub FasterThanLooping()

Workbooks.Add xlWBATWorksheet
Range("A1:A10000") = "Baked Beans Buy Zeinz Products Today at 10:03:42am"

ActiveWorkbook.Names.Add Name:="splitter", RefersToR1C1:= _
"=EVALUATE(""={""""""&SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(RC1,""
Buy "",""|Buy|""),"" Sell "",""|Sell|""),"" Today at
"",""|Today|At|""),""|"",CHAR(34)&INDEX(GET.WORKSP ACE(37),14)&CHAR(34))
&""""""}"")"

With Range("B1:F10000")
.Rows(1).FormulaArray = "=splitter"
.FillDown
.Value=.value
End With

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

You might want to add that if you copy a cell with this formula and
paste it to another worksheet, you will get a general protection fault
and your workbook will close losing all unsaved changes. this is in
xl2000 and earlier. I don't believe you get the failure in xl2002 and
later.