Extract Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peanut - 05 May 2008 16:49 GMT

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.

Reply to this Message

Reitanos - 05 May 2008 17:05 GMT

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

> 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
[quoted text clipped - 8 lines]
>
> But I'm not sure how to obtain the other information. Please help.

Reply to this Message

Peanut - 05 May 2008 17:20 GMT

That is a lot simpler than what I was trying to make it. Thank you!

> Once you find the year, you can use left to pick up the rest of the
> date:
[quoted text clipped - 12 lines]
> >
> > But I'm not sure how to obtain the other information. Please help.

Reply to this Message

Ron Rosenfeld - 05 May 2008 17:43 GMT

>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
[quoted text clipped - 8 lines]
>
>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 to this Message