Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Text
I have a spreadsheet with a daily import tab. In this daily import, there is
a cell with the date as "April 4, 2006 As of 2:00 PM ET". I am trying to extract the date of the imported worksheet for display on another page. Because of the dynamics of this spreadsheet, Text To Columns will not work for me. Is there an easy way to extract the information since the lengths of the month and date may vary? I got the year by =--MID(L1,FIND(", ",L1)+2,4) But I'm not sure how to obtain the other information. Please help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Text
Once you find the year, you can use left to pick up the rest of the
date: =LEFT(L1,FIND("As",L1)-2) On May 5, 11:49 am, Peanut wrote: I have a spreadsheet with a daily import tab. In this daily import, there is a cell with the date as "April 4, 2006 As of 2:00 PM ET". I am trying to extract the date of the imported worksheet for display on another page. Because of the dynamics of this spreadsheet, Text To Columns will not work for me. Is there an easy way to extract the information since the lengths of the month and date may vary? I got the year by =--MID(L1,FIND(", ",L1)+2,4) But I'm not sure how to obtain the other information. Please help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Text
That is a lot simpler than what I was trying to make it. Thank you!
"Reitanos" wrote: Once you find the year, you can use left to pick up the rest of the date: =LEFT(L1,FIND("As",L1)-2) On May 5, 11:49 am, Peanut wrote: I have a spreadsheet with a daily import tab. In this daily import, there is a cell with the date as "April 4, 2006 As of 2:00 PM ET". I am trying to extract the date of the imported worksheet for display on another page. Because of the dynamics of this spreadsheet, Text To Columns will not work for me. Is there an easy way to extract the information since the lengths of the month and date may vary? I got the year by =--MID(L1,FIND(", ",L1)+2,4) But I'm not sure how to obtain the other information. Please help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Text
On Mon, 5 May 2008 08:49:03 -0700, Peanut
wrote: I have a spreadsheet with a daily import tab. In this daily import, there is a cell with the date as "April 4, 2006 As of 2:00 PM ET". I am trying to extract the date of the imported worksheet for display on another page. Because of the dynamics of this spreadsheet, Text To Columns will not work for me. Is there an easy way to extract the information since the lengths of the month and date may vary? I got the year by =--MID(L1,FIND(", ",L1)+2,4) But I'm not sure how to obtain the other information. Please help. Given your format, the date will always end at the third space. =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-1) returns the date in text form. Since the date is an unambiguous textual representation, you could convert it into an Excel date by preceding it with the double unary, and formatting it as a date. e.g. =--LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-1) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to extract certain text from text string | Excel Worksheet Functions | |||
Extract text from large Text | Excel Discussion (Misc queries) | |||
Text Extract | Excel Worksheet Functions | |||
EXTRACT TEXT FROM TEXT STRING | Excel Worksheet Functions | |||
Extract text | Excel Worksheet Functions |