Trying to seperate a lot of vertical text from a single cell.

**Wendy Collins** · 01-29-2013, 07:08 AM

Hi
I have been sent an export from Outlook in a .csv file. The Body of the Email is all contained in one cell for each row (each email detail) and is in the following vertical format within the cell:

Column Header
blank row
Data
blank row
Column Header
blank row
Data
blank row

....and so on. I've tried to do something using Transpose and Text to Columns, but I've not been able to successfully seperate out the data into a useful format in Excel. The data are responses to a website and contain Account/Contact information which I hope to be able to import into a CRM system as new Leads.
Can anyone suggest a way of extracting this information, so that I can get each row into a format that gives me the column header with the related data below, as you'd normally expect to see in Excel?

Thank you!

Wendy

**Pepe Le Mokko** · 01-29-2013, 03:38 PM

Please post a sample sheet

**Wendy Collins** · 01-30-2013, 09:42 AM

Hi
I hope that you are able to see the attached file.........ABA.xlsx
Wendy

**WHER** · 01-30-2013, 08:10 PM

see cells T2:U2 in attachment.

**Wendy Collins** · 01-31-2013, 07:35 AM

Fantastic - thank you!

**FDibbins** · 01-30-2013, 08:47 PM

I used a very similar approach...

=SUBSTITUTE(SUBSTITUTE(B2,CHAR(10),""),CHAR(13),"|")
because Im not sure which column which bit of info goes into, you can either use text-to-columns, as suggested, or, if you make the column headings identical to what will be in the body of the text, you can use a combination of mid(search(len())) to extract the vairious bits and pieces, based on the headings