software

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(quot; quot;,A1,FIND(quot;[quot;,A1)) 1,(SEARCH(quot;.xlsquot;,A1)-1)-FIND(quot;
quot;,A1,FIND(quot;[quot;,A1)))

Hope this helps!

In article gt;,
quot;Barb Reinhardtquot; gt; wrote:

gt; Let's say I have a string that looks like this
gt;
gt; C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;
gt; I want to extract the DATE prior to .xls. I can get to this:
gt; C:\Documents and Settings\me\[test 2-2006
gt;
gt; It is possible that the filename would have numbers in it prior to the date.
gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt; suggestions?
gt;
gt; Thanks,
gt; Barb Reinhardt

Hi Barb,

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

Regards,
KL

quot;Barb Reinhardtquot; gt; wrote in message
...
gt; Let's say I have a string that looks like this
gt;
gt; C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;
gt; I want to extract the DATE prior to .xls. I can get to this:
gt; C:\Documents and Settings\me\[test 2-2006
gt;
gt; It is possible that the filename would have numbers in it prior to the date.
gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt; suggestions?
gt;
gt; Thanks,
gt; Barb Reinhardt
gt;
gt;
if the date is always preceded by a space and in the format m-yyyy or
mm-yyyy

=TRIM(MID(A1,FIND(quot;.xlsquot;,A1)-7,7))

or if you can't guarantee that and you just want all text between the
space and the quot;.xlsquot;

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(quot;.xlsquot;,A1)-1),quot; quot;,REPT(quot;
quot;,99)),99))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=508295On Fri, 3 Feb 2006 12:28:27 -0800, quot;Barb Reinhardtquot;
gt; wrote:

gt;Let's say I have a string that looks like this
gt;
gt;C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;
gt;I want to extract the DATE prior to .xls. I can get to this:
gt;C:\Documents and Settings\me\[test 2-2006
gt;
gt;It is possible that the filename would have numbers in it prior to the date.
gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt;suggestions?
gt;
gt;Thanks,
gt;Barb Reinhardt
gt;

You could use Regular Expressions.

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

Then try this formula:

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

The expression quot;\b[012]?\d-\d{4}(?=\.xls)quot; looks for a string which
starts with the word boundary (after the lt;spacegt; in your example)
followed by some number in the range of 1-12 (with an optional leading
zero.
followed by a dash lt;-gt;
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},A1amp;quot;01234 56789quot;)),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-2006Does that help?

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

XL2002, WinXP-Proquot;Barb Reinhardtquot; wrote:

gt; Let's say I have a string that looks like this
gt;
gt; C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;
gt; I want to extract the DATE prior to .xls. I can get to this:
gt; C:\Documents and Settings\me\[test 2-2006
gt;
gt; It is possible that the filename would have numbers in it prior to the date.
gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt; suggestions?
gt;
gt; Thanks,
gt; Barb Reinhardt
gt;
gt;

On Sat, 4 Feb 2006 09:11:20 -0800, quot;Ron Coderrequot;
gt; wrote:

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

Not the case -- reread the original postgt;
gt;For text in A1
gt;B1:
gt;=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123 456789quot;)),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1) ),1))))
gt;
gt;Example:
gt;For A1: C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;That formula returns 2-2006
gt;
gt;
gt;Does that help?
gt;
gt;***********
gt;Regards,
gt;Ron
gt;
gt;XL2002, WinXP-Pro
gt;
gt;
gt;quot;Barb Reinhardtquot; wrote:
gt;
gt;gt; Let's say I have a string that looks like this
gt;gt;
gt;gt; C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;gt;
gt;gt; I want to extract the DATE prior to .xls. I can get to this:
gt;gt; C:\Documents and Settings\me\[test 2-2006
gt;gt;
gt;gt; It is possible that the filename would have numbers in it prior to the date.
gt;gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt;gt; suggestions?
gt;gt;
gt;gt; Thanks,
gt;gt; Barb Reinhardt
gt;gt;
gt;gt;

--ron

Ron Rosenfeld wrote...
....
gt;Then try this formula:
gt;
gt;=REGEX.MID(A1,quot;\b[012]?\d-\d{4}(?=\.xls)quot;)
gt;
gt;The expression quot;\b[012]?\d-\d{4}(?=\.xls)quot; looks for a string which
gt; starts with the word boundary (after the lt;spacegt; in your example)
gt; 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...
gt;If there will always be a space prior to the date, try...
gt;
gt;=MID(A1,FIND(quot; quot;,A1,FIND(quot;[quot;,A1)) 1,(SEARCH(quot;.xlsquot;,A1)-1)-FIND(quot; quot;,
gt;A1,FIND(quot;[quot;,A1)))
....

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

=RIGHT(LEFT(A1,FIND(quot;.xls]quot;,A1)-1),6 ISNUMBER(-MID(A1,FIND(quot;.xls]quot;,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 Studentquot;Barb Reinhardtquot; wrote:

gt; Let's say I have a string that looks like this
gt;
gt; C:\Documents and Settings\me\[test 2-2006.xls]Sheet1
gt;
gt; I want to extract the DATE prior to .xls. I can get to this:
gt; C:\Documents and Settings\me\[test 2-2006
gt;
gt; It is possible that the filename would have numbers in it prior to the date.
gt; I'm trying to figure out a way to get to the last SPACE in the string. Any
gt; suggestions?
gt;
gt; Thanks,
gt; Barb Reinhardt
gt;
gt;