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

If there will always be a space prior to the date, try...

=MID(A1,FIND(" ",A1,FIND("[",A1))+1,(SEARCH(".xls",A1)-1)-FIND("
",A1,FIND("[",A1)))

Hope this helps!

In article ,
"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

Hi Barb,

Try this:
=--SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,7),".","")

Regards,
KL

"Barb Reinhardt" wrote in message
...
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

if the date is always preceded by a space and in the format m-yyyy or
mm-yyyy

=TRIM(MID(A1,FIND(".xls",A1)-7,7))

or if you can't guarantee that and you just want all text between the
space and the ".xls"

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(".xls",A1)-1)," ",REPT("
",99)),99))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508295

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

If there will be NO other numbers in the string, try something like this:

For text in A1
B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1) ),1))))

Example:
For A1: C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
That formula returns 2-2006


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"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

On Sat, 4 Feb 2006 09:11:20 -0800, "Ron Coderre"
wrote:

If there will be NO other numbers in the string, try something like this:

Not the case -- reread the original post



For text in A1
B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1) ),1))))

Example:
For A1: C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
That formula returns 2-2006


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"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



--ron

Ron Rosenfeld wrote...
....
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.
....

This would match 00, 13, 14, . . ., 29, none of which are valid month
numbers. If you truly want to limit this piece just to valid month
numbers, then you need something like

(0?[1-9]|1[0-2])

The most efficent way to learn regular expressions is to respond to
regexp questions in Unix and scripting language newsgroups and enjoy
the feedback.

Domenic wrote...
If there will always be a space prior to the date, try...

=MID(A1,FIND(" ",A1,FIND("[",A1))+1,(SEARCH(".xls",A1)-1)-FIND(" ",
A1,FIND("[",A1)))
....

Better perhaps to assume nothing more than that the date substring
immediately precedes the ".xls]" substring and is formatted as either
m-yyyy or mm-yyyy. If so,

=RIGHT(LEFT(A1,FIND(".xls]",A1)-1),6+ISNUMBER(-MID(A1,FIND(".xls]",A1)-7,1)))

You have already stripped off the .xls part

Say you have C:\Documents and Settings\me\[test 2-2006 in Z100. Then
=TRIM(RIGHT(Z100,7)) will get the 6-2006 or say 10-2006 part and remove the
leading SPACE if there is one
..
--
Gary''s Student


"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

"Harlan Grove" wrote
=RIGHT(LEFT(A1,FIND(".xls]",A1)-1),6+ISNUMBER(-MID(A1,FIND(".xls]",A1)-7,1)))

May be [building on your idea] even like this:

=LOOKUP(1E+307,--RIGHT(LEFT(A1,FIND(".xls]",A1)-1),{6,7}))

Regards,
KL

In article .com,
"Harlan Grove" wrote:

Better perhaps to assume nothing more than that the date substring
immediately precedes the ".xls]" substring and is formatted as either
m-yyyy or mm-yyyy. If so,

=RIGHT(LEFT(A1,FIND(".xls]",A1)-1),6+ISNUMBER(-MID(A1,FIND(".xls]",A1)-7,1)))

Beautiful! I had no idea how to construct a formula for the above
mentioned assumption. Now that you've shown me how, it seems so
simple... :)

Thanks Harlan! Much appreciated!

On 4 Feb 2006 09:52:26 -0800, "Harlan Grove" wrote:

The most efficent way to learn regular expressions is to respond to
regexp questions in Unix and scripting language newsgroups and enjoy
the feedback.

I have been reading the two ng's you recommended, (and also making my way
through the book you recommended), but responding on those ng's is difficult as
they are either unix or perl -centric.

So while I might be able to devise a regex in response to a request, I have no
facility (or ability) to express it in the language they're looking for. So
far, they have provided useful exercises (obviously not useful enough, in view
of this now obvious error of mine), but I have not felt in a position to post a
response.
--ron

KL wrote...
"Harlan Grove" wrote
=RIGHT(LEFT(A1,FIND(".xls]",A1)-1),6+ISNUMBER(-MID(A1,FIND(".xls]",A1)-7,1)))

May be [building on your idea] even like this:

=LOOKUP(1E+307,--RIGHT(LEFT(A1,FIND(".xls]",A1)-1),{6,7}))

Yeah, that'd work as long as you format it as m-yyyy.