How to split up bulk text??????

Hi everyone, i'm really stuck with this one....

I have recently been charged with exporting data from my companies ACT! database and putting into excel ready to go into our new CRM package.

I used an ACT! exporter that has left me a spreadsheet with data that I cant use yet.

The notes for a particular contact are not split up into different cells as they should be, rather they are all put into one cell with the dates in as such-

23/11/2000 quick brown fox jumped over the blah blah 24/11/2004 quick brown fox jumped over the blah blah 26/04/2005 quick brown fox jumped over the blah blah 27/04/2005 quick brown fox jumped over the blah blah 28/04/2005 quick brown fox jumped over the blah blah

basically every time there is a date i want excel to move the data before the next date into a new cell or at least a new line so i can copy/paste a new line.

Please can anyone help!!!!!!!!!!!

Thanks in advance!

DJS,
Is the number of words between dates always the same?

Kostis Vezerides

No unfortunately not, they are all of variable lengths.

DJS,
never mind the question. Here is a quick solution, even for the case of
variable number of words per sentence.

In the following, I am assuming that there are no numeric values in
your data: only dates and words. I am also assuming that your imported
text is in cell A1 (BTW, the maximum an Excel cell can hold is 32767
characters, so you might have lost data anyway).

In column B:B we will place the positions of the spaces, which are our
delimiters:
B1: =FIND(" ", $A$1)
B2: =FIND(" ", $A$1, B1+1). Copy B2 formula down until you see #VALUE!

In column C:C we will put the individual words, preserving the date
format.
C1: =MID($A$1,1,B1-1)
C2: =MID($A$1,B1+1,B2-B1-1). Copy C2 down.

In column D:D we will gradually build the lines from the broken words.
D1: =MID(A1,1,C1-1)
D2: =IF(NOT(ISERROR(VALUE(C2))),TEXT(C2,"mm/dd/yyyy"),D1&" "&TEXT(C2,
"mm/dd/yyyy")). Copy D2 down.

Finally, in column E:E we will leave only full lines.
E1: =IF(NOT(ISERROR(VALUE(D2))),D1,""). Copy down as necessary

At the end of this process, you are left with a column with most rows
blank. The non-blank rows will be the ones with the imported lines.

Edit|Copy column E:E.
Edit|Paste Special in F1. Check the Values checkbox.

Now column F:F contains the data amongst many empty lines. Sort column
F:F and you will have all the lines together.

HTH
Kostis Vezerides

Thanks for that, you say that you assume there is no number ic data, the only numeric data is the date which is in 24/01/2005 format.

Is that ok?

Cheers

Yes, if the only numeric data is dates, this setup should work fine.

Kostis