On Fri, 3 Feb 2006 12:28:27 -0800, "Barb Reinhardt"
wrote:

Let's say I have a string that looks like this

C:\Documents and Settings\me\[test 2-2006.xls]Sheet1

I want to extract the DATE prior to .xls. I can get to this:
C:\Documents and Settings\me\[test 2-2006

It is possible that the filename would have numbers in it prior to the date.
I'm trying to figure out a way to get to the last SPACE in the string. Any
suggestions?

Thanks,
Barb Reinhardt


You could use Regular Expressions.

Download and install Longre's free morefunc.xll add-in from

Then try this formula:

=REGEX.MID(A1,"\b[012]?\d-\d{4}(?=\.xls)")

The expression "\b[012]?\d-\d{4}(?=\.xls)" looks for a string which
starts with the word boundary (after the <space in your example)
followed by some number in the range of 1-12 (with an optional leading
zero.
followed by a dash <-
followed by 4 digits.
followed by .xls

(but don't return the .xls)

I think that logic should cover all of the possibilities. But if the first
digits represent a day number, and not a month number as I assumed, then we may
need to change the logic.


--ron