i have about 1000 emails in outlook that are automatic responces from
our website giving me peoples registration details
each bit of info is on a seperate line
when i export these emails out into an excel spreadsheet it puts all
the info into one column with 1000 rows with all the info in
when i try and do "text to columns" it wont seperate them out, as the
seperate bits of info are all on seperate lines within each row

there are about 4 lines in each of the 1000 rows and i want each line
to be a seperate column

any one got any ideas ?


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

When you say "about 4 lines": does the number of lines vary from email
to email? If it does not, you could separate the lines into columns
with a formula in each of the target columns: =A1, =A2, =A3, =A4. Then
paste those formulas into row 5, and so forth, and remove blanks by
sorting.

As an alternative, the PST file Outlook uses to store email is a
database. If you use MS Access you can import the PST file (or just a
folder from the PST file) into an Access table, which will provide you
all sorts of info in addition to the text of the email: sender's email
address, send time, receive time, etc.

you might be able to parse the text in the cell with a formula, provided
there's a regular identifier for each field in the cell, like "Name: ",
"E-mail: " etc. Then you could have your formula look for those identifying
words and pull out what comes after it. I'd have to see a sample of the data
in the cell in order to understand what the formula would have to look like.

"aquagas" wrote:


i have about 1000 emails in outlook that are automatic responces from
our website giving me peoples registration details
each bit of info is on a seperate line
when i export these emails out into an excel spreadsheet it puts all
the info into one column with 1000 rows with all the info in
when i try and do "text to columns" it wont seperate them out, as the
seperate bits of info are all on seperate lines within each row

there are about 4 lines in each of the 1000 rows and i want each line
to be a seperate column

any one got any ideas ?


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

ok i've added a sample of what i'm looking at
the idea of pulling out everything after the
name:
email:
sounds good to me if, someone could tell me how to do it


+-------------------------------------------------------------------+
|Filename: test3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4619 |
+-------------------------------------------------------------------+

--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

also the lines are lines in a row
so a1 has say 4 lines in it
a2 has 5 lines in it - depending how much of an address they type in

so i'm not sure that last bit of help would work ?


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

can someone tell me how to do parsing as i dont really know what this is
let alone how to do it ?

heres an example of the text in each row that i need seperating out :

*-'A visitor at aqua gas has left the following information

Name: the name of the person registering

Email: email address of the person registering
whoever commented:

Address of the person registering


Send Date: 19/08/2005 14:46:15-*


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

I'm having the exact same issue.
I'd be very interested to know what you find out.
~ Brian


"aquagas" wrote:


can someone tell me how to do parsing as i dont really know what this is
let alone how to do it ?

heres an example of the text in each row that i need seperating out :

*-'A visitor at aqua gas has left the following information

Name: the name of the person registering

Email: email address of the person registering
whoever commented:

Address of the person registering


Send Date: 19/08/2005 14:46:15-*


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939

You could try copying the Excel into Word and using replace to change
hard returns into an unusual character. Then take this back into Excel
and use text to columns.


--
mrice


------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=531939

That sounds good - how do you replace a hard return in word
Ive tried pressing return in the space or copying and pasting into it -
nothing seems to work
looks like everything is against me on this one !


--
aquagas
------------------------------------------------------------------------
aquagas's Profile: http://www.excelforum.com/member.php...o&userid=33365
View this thread: http://www.excelforum.com/showthread...hreadid=531939