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!

 

[KL]

Hi Barb,

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

Regards,
KL

 

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

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

 

[Ron Rosenfeld]

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

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

 

[KL]

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

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]

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

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