Find numeric value at end of string

[Barb Reinhardt]

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

[Domenic]

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 <[email protected]>,
"Barb Reinhardt" <[email protected]> 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

[KL]

Hi Barb,

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

Regards,
KL

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[daddylonglegs]

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))

[Ron Rosenfeld]

On Fri, 3 Feb 2006 12:28:27 -0800, "Barb Reinhardt"
<[email protected]> 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

[Ron Coderre]

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&"0123456789")),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 Rosenfeld]

On Sat, 4 Feb 2006 09:11:20 -0800, "Ron Coderre"
<[email protected]> 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&"0123456789")),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

[Harlan Grove]

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.

[Harlan Grove]

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)))

[Gary''s Student]

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
>
>

[KL]

"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

[Domenic]

In article <[email protected]>,
"Harlan Grove" <[email protected]> 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!

[Ron Rosenfeld]

On 4 Feb 2006 09:52:26 -0800, "Harlan Grove" <[email protected]> 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

[Harlan Grove]

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.