Extract Text

P

Peanut

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.
 
R

Reitanos

Once you find the year, you can use left to pick up the rest of the
date:
=LEFT(L1,FIND("As",L1)-2)
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
Top