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

  • Thread starter Thread starter DJS1234
  • Start date Start date
D

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!
 
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, th
only numeric data is the date which is in 24/01/2005 format.

Is that ok?

Cheer
 
Back
Top