Separating data from cells
 

Hi Ed,

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

Justin

Separating data from cells
 

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.


"Justin Case" wrote in message
...
Hi Ed,

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

Justin

Separating data from cells
 

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

Separating data from cells
 

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.

"Justin Case" wrote in message
...
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

Separating data from cells
 

Is there a limitation to the size of the email I can send to this newsgroup?

Ed.

"Justin Case" wrote in message
...
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

Separating data from cells
 

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.



"Justin Case" wrote in message
...
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

Separating data from cells
 

Hello Justin,

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

Thank you very much.

Ed.

"Justin Case" wrote in message
...
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