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

[DJS1234]

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!

 

[vezerid]

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

Kostis Vezerides

 

[vezerid]

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

 

[DJS1234]

No unfortunately not, they are all of variable lengths

 

[DJS1234]

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

Is that ok?

Cheer

 

[vezerid]

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

Kostis