Separating data from cells

E

Ed

Hello,

I have over 500 records in excel that are similar to the following example
record. However, each row contains the entire line in one cell. Is there a
way to separate the headers from the data; assign each header to a column;
and assign the data to its respective header column? (i.e. Column A =
"Date"; B = "From"; C = "Subject", etc..)

Date: 16 MAY 2009
From (e-mail address removed)
Subject: DISTRIBUTION
Company_Name: XYZ COMPANY
Title: MR.
First_Name: JOHN
Last_Name: SMITH
Position_in_Company: MANAGER
Street_Address_1: PO BOX 1234
Street_Address_2:
City: NEW YORK
State: NY
Zip_Postal_Code: 100115
Country: USA
Telephone: 212-555-1212
Fax: 212-555-1213
Cell_Mobile: 212-555-1214
Email_Address: (e-mail address removed)
Re_Confirm_Email_Address: (e-mail address removed)
Website_Address: XYZ.COM
Countries_you_Sell_to: USA & CANADA
Products_you_currently_sell: CONSUMER GOODS
Companies_you_sell_to: RETAILERS


Thank you in advance.

Ed.
 
H

HelpExcel.com

Ed,

You can accomplish this with a small amount of VBA. Everything to the left
of the colon will be parsed out.
 
H

HelpExcel.com

Ed,

You can accomplish this with a small amount of VBA. Everything to the left
of the colon will be parsed out.
 
J

Justin Case

Hi Ed,

Are you referring to multiple sheets? Or are there multiple records in
the same row?

Justin
 
J

Justin Case

Hi Ed,

Are you referring to multiple sheets? Or are there multiple records in
the same row?

Justin
 
E

Ed

Hello,

Actually, what I mean by Records is "contact information for 500 different
companies". The 500 different contacts are all in the same column.

Ed.
 
E

Ed

Hello,

Actually, what I mean by Records is "contact information for 500 different
companies". The 500 different contacts are all in the same column.

Ed.
 
J

Justin Case

Hi guys,

Coupla problems...

The "From" field doesn't contain a colon. Some fields are not
populated. So try this formula approach

The following formula, copied down in another column to all necessary
rows will get the data:

=MID(TRIM(A1),FIND(":",A1)+2,LEN(A1))

for the "From" field (with that frustrating missing colon), use..

=MID(TRIM(A2),FIND("From ",A2)+6,LEN(A2))

Finally, select this group of cells and >COPY. Move to the target cell
(anywhere on the sheet where you're headings are, or another sheet or
even workbook) then right click choose >PASTE-SPECIAL, select "Values"
& "Transpose" in the dialog box. Press OK.

This last part will work, but would be a pain to do 500 times, so
where exactly the data is, as I asked about in my earlier post, will
be critical in order to use VBA as Eddie mentioned, which of couse
would be faster.

Let us know...

Regards,
Justin
 
J

Justin Case

Hi guys,

Coupla problems...

The "From" field doesn't contain a colon. Some fields are not
populated. So try this formula approach

The following formula, copied down in another column to all necessary
rows will get the data:

=MID(TRIM(A1),FIND(":",A1)+2,LEN(A1))

for the "From" field (with that frustrating missing colon), use..

=MID(TRIM(A2),FIND("From ",A2)+6,LEN(A2))

Finally, select this group of cells and >COPY. Move to the target cell
(anywhere on the sheet where you're headings are, or another sheet or
even workbook) then right click choose >PASTE-SPECIAL, select "Values"
& "Transpose" in the dialog box. Press OK.

This last part will work, but would be a pain to do 500 times, so
where exactly the data is, as I asked about in my earlier post, will
be critical in order to use VBA as Eddie mentioned, which of couse
would be faster.

Let us know...

Regards,
Justin
 
E

Ed

To answer your question regarding where the data is located, the data for
each of the 500 companies is located in a single column (column "A") one
after the other starting with the "Date". Following is an example of what
two (2) companies look like in the Excel column. There are no empty cells
separating the companies. Also, I've added a colon after the "From".


Date: 16 MAY 2009
From: (e-mail address removed)
Subject: DISTRIBUTION
Company_Name: XYZ COMPANY
Title: MR.
First_Name: JOHN
Last_Name: SMITH
Position_in_Company: MANAGER
Street_Address_1: PO BOX 1234
Street_Address_2:
City: NEW YORK
State: NY
Zip_Postal_Code: 100115
Country: USA
Telephone: 212-555-1212
Fax: 212-555-1213
Cell_Mobile: 212-555-1214
Email_Address: (e-mail address removed)
Re_Confirm_Email_Address: (e-mail address removed)
Website_Address: XYZ.COM
Countries_you_Sell_to: USA & CANADA
Products_you_currently_sell: CONSUMER GOODS
Companies_you_sell_to: RETAILERS
Date: 14 MAY 2009
From: (e-mail address removed)
Subject: DISTRIBUTION
Company_Name: ABC COMPANY
Title: MS.
First_Name: MARY
Last_Name: JOHNSON
Position_in_Company: DIRECTOR
Street_Address_1: PO BOX 1234
Street_Address_2:
City: MIAMI
State: FL
Zip_Postal_Code: 100115
Country: USA
Telephone: 212-555-1212
Fax: 212-555-1213
Cell_Mobile: 212-555-1214
Email_Address: (e-mail address removed)
Re_Confirm_Email_Address: (e-mail address removed)
Website_Address: ABC.COM
Countries_you_Sell_to: USA & CANADA
Products_you_currently_sell: CONSUMER GOODS
Companies_you_sell_to: RETAILERS


Ed.
 
E

Ed

To answer your question regarding where the data is located, the data for
each of the 500 companies is located in a single column (column "A") one
after the other starting with the "Date". Following is an example of what
two (2) companies look like in the Excel column. There are no empty cells
separating the companies. Also, I've added a colon after the "From".


Date: 16 MAY 2009
From: (e-mail address removed)
Subject: DISTRIBUTION
Company_Name: XYZ COMPANY
Title: MR.
First_Name: JOHN
Last_Name: SMITH
Position_in_Company: MANAGER
Street_Address_1: PO BOX 1234
Street_Address_2:
City: NEW YORK
State: NY
Zip_Postal_Code: 100115
Country: USA
Telephone: 212-555-1212
Fax: 212-555-1213
Cell_Mobile: 212-555-1214
Email_Address: (e-mail address removed)
Re_Confirm_Email_Address: (e-mail address removed)
Website_Address: XYZ.COM
Countries_you_Sell_to: USA & CANADA
Products_you_currently_sell: CONSUMER GOODS
Companies_you_sell_to: RETAILERS
Date: 14 MAY 2009
From: (e-mail address removed)
Subject: DISTRIBUTION
Company_Name: ABC COMPANY
Title: MS.
First_Name: MARY
Last_Name: JOHNSON
Position_in_Company: DIRECTOR
Street_Address_1: PO BOX 1234
Street_Address_2:
City: MIAMI
State: FL
Zip_Postal_Code: 100115
Country: USA
Telephone: 212-555-1212
Fax: 212-555-1213
Cell_Mobile: 212-555-1214
Email_Address: (e-mail address removed)
Re_Confirm_Email_Address: (e-mail address removed)
Website_Address: ABC.COM
Countries_you_Sell_to: USA & CANADA
Products_you_currently_sell: CONSUMER GOODS
Companies_you_sell_to: RETAILERS


Ed.
 
E

Ed

For some reason my response is not showing up. I sent it twice. I'm not
sure if this email will go through either.

Ed.
 
E

Ed

For some reason my response is not showing up. I sent it twice. I'm not
sure if this email will go through either.

Ed.
 
E

Ed

To answer your question regarding where the data is located, the data for
each of the 500 companies is located in a single column (column "A") one
after the other starting with the "Date". There are no empty cells
separating the companies. Also, I've added a colon after the "From". I
tried to send an email with an example of what it actually looks like but
didn't go through.

Column "A"
Date:
From:
Subject:
.....
.....
.....
Companies_you_sell_to:
Date:
From:
Subject:
.....
.....
.....
Companies_you_sell_to:
Etc..


Ed.
 
E

Ed

To answer your question regarding where the data is located, the data for
each of the 500 companies is located in a single column (column "A") one
after the other starting with the "Date". There are no empty cells
separating the companies. Also, I've added a colon after the "From". I
tried to send an email with an example of what it actually looks like but
didn't go through.

Column "A"
Date:
From:
Subject:
.....
.....
.....
Companies_you_sell_to:
Date:
From:
Subject:
.....
.....
.....
Companies_you_sell_to:
Etc..


Ed.
 
E

Ed

Hello Justin,

I was wondering if there is a VBA solution for changing the data location?

Thank you very much.

Ed.
 
E

Ed

Hello Justin,

I was wondering if there is a VBA solution for changing the data location?

Thank you very much.

Ed.
 
Top