Big problem

[Ana]

Hi everybody!

My problem is : I get data from internet in a sheet, I
have to do it every day. I have in cell A1 the following
sentence :

Daily Returns for Major Fixed Income Indexes for May 21
2004 in EURO Terms

and I need the date , every day there will be a diferent
date ; My problem is to obtain from that text the exact
date in a date format .. How can I do it ?
I have tried to use formules like right and left , but as
is a variable text , I do not get always the excact date
and of course it is not in a date format .

anyone have any idea ??
Thanks a lot
Ana

 

[Frank Kabel]

Hi
is this date always at the same position?

 

[Ed]

Hi, Ana. If you always get the information for "today's date", whatever
that is, couldn't you just insert "today's date" instead of trying to grab
the date from the file?

If the date is always different, but consistently different (that is, always
either one or two days before), then you could calculate based on today's
date.

If the date has no kind of consistency, you might try setting up a search
using InStr to find "2004", then a combination of Left, Mid, and Right to
pull out the date. That would keep you going for at least 6 more months.
8>)

Personally, I would capture the A1 value into a string, pass it into Word,
and set up something in Word VBA which has the ability to move a range by
words. So if a search found "2004", I could collapse the range to the end
of 2004, then extend it back three words to encompass all of Month Day Year
without worrying about how many characters are in the month name or the
date. Capture that, and I could write it back to the spreadsheet. I would
do it this way because I don't know if Excel VBA knows what a "word" is.

HTH
Ed