Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating data from cells
Hi Ed,
Are you referring to multiple sheets? Or are there multiple records in the same row? Justin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating carriage return data into separate cells | Excel Discussion (Misc queries) | |||
Separating Numerical and Alpha Data in cells | Excel Discussion (Misc queries) | |||
Separating Data in one cell to multiple cells | Excel Discussion (Misc queries) | |||
Separating Two Data Sets from one Excel Cell into Two Cells | Excel Discussion (Misc queries) | |||
separating text from cells | Excel Discussion (Misc queries) |