Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Separating data from cells

Hi Ed,

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

Justin
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating carriage return data into separate cells Richard Excel Discussion (Misc queries) 2 May 11th 09 09:12 PM
Separating Numerical and Alpha Data in cells Larry Excel Discussion (Misc queries) 6 June 25th 07 09:03 PM
Separating Data in one cell to multiple cells Supe Excel Discussion (Misc queries) 1 February 27th 07 05:26 PM
Separating Two Data Sets from one Excel Cell into Two Cells mpstockdale Excel Discussion (Misc queries) 9 October 8th 06 12:25 PM
separating text from cells kikilein Excel Discussion (Misc queries) 16 July 16th 06 08:12 PM


All times are GMT +1. The time now is 11:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"