finding a value in a string

[....]
If it weren't for the filename

ABC_DEF123_IJK_6_0.xls

you could use the formula

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(A1,9),5),
6-INT((ROW(INDIRECT("1:25"))-1)/5),
1+MOD(ROW(INDIRECT("1:25"))-1,5))))

to pull the version number from the filename in cell A1. I don't
think
there's any compact way to handle underscores between numerals as
decimal points, but I could be wrong.

Hi Harlan
using two formulas (to avoid the maximum of 7 nested functions) One
could use:
B1:
=SUBSTITUTE(A1,"_",".",MAX(LEN(A1)-LEN(SUBSTITUTE(A1,"_","")),1))

C1:
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(B1,9),5),6-INT((ROW(INDIRECT("1:25"))
-1)/5),1+MOD(ROW(INDIRECT("1:25"))-1,5))))

This works correctly for the following test data:
ABC_DEF123_IJK_V(1).xls
ABC_DEF123_IJK_V2.xls
ABC_DEF123_IJK_V3_A.xls
ABC_DEF123_IJK_V4_NA.xls
ABC_DEF123_IJK_V5,1.xls
ABC_DEF123_IJK6_2.xls
ABC DEF123 IJK V7.xls
ABC,DEF123,IJK6,2.xls
ABC_DEF123_IJK_6_2.xls
ABC_DEF123_IJK_6_0.xls

Frank

hgrove wrote...
...
If it weren't for the filename

ABC_DEF123_IJK_6_0.xls

you could use the formula

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(A1,9),5),
6-INT((ROW(INDIRECT("1:25"))-1)/5),
1+MOD(ROW(INDIRECT("1:25"))-1,5))))

to pull the version number from the filename in cell A1. I don't
think there's any compact way to handle underscores between
numerals as decimal points, but I could be wrong.

I was wrong. The following *nonarray* formula handles underscores as
decimal points as long as there are at most 2 of them in the 5 chars to
the left of '.xls'.

=LOOKUP(99999,
ABS(-MID(SUBSTITUTE(LEFT(RIGHT(F10,9),5),"_",".",
1+MOD(ROW(INDIRECT("1:50")),2)),
6-MOD(INT((ROW(INDIRECT("1:50"))-1)/5),5),
1+MOD(ROW(INDIRECT("1:50"))-1,5))))


---
Message posted from http://www.ExcelForum.com/

Frank Kabel wrote:
[....]

Hi Harlan
using two formulas (to avoid the maximum of 7 nested functions) One
could use:
B1:
=SUBSTITUTE(A1,"_",".",MAX(LEN(A1)-LEN(SUBSTITUTE(A1,"_","")),1))

C1:

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(B1,9),5),6-INT((ROW(INDIRECT("1:25"))
-1)/5),1+MOD(ROW(INDIRECT("1:25"))-1,5))))


And to create one single formula for this one may use:
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",",",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))

with seq as a defined name for
=ROW(INDIRECT("1:25"))-1

Now one can just argue if this is a 'compact' formula :-)

Frank

hgrove wrote...
..
I was wrong. The following *nonarray* formula handles underscores as
decimal points as long as there are at most 2 of them in the 5 chars
to the left of '.xls'.

=LOOKUP(99999,
ABS(-MID(SUBSTITUTE(LEFT(RIGHT(F10,9),5),"_",".",
1+MOD(ROW(INDIRECT("1:50")),2)),
6-MOD(INT((ROW(INDIRECT("1:50"))-1)/5),5),
1+MOD(ROW(INDIRECT("1:50"))-1,5))))

Hi Harlan
very impressive. Though this won't handle:
ABC_DEF123_IJK_6_2.xls

returns 38023.

Frank

Frank Kabel wrote:
Frank Kabel wrote:
[....]

And to create one single formula for this one may use:

=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",",",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))

Make this
=LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN
(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5))))

for the dot as decimal point (and not the coma)


with seq as a defined name for
=ROW(INDIRECT("1:25"))-1

Now one can just argue if this is a 'compact' formula :-)

Frank

Frank Kabel wrote...
...
using two formulas (to avoid the maximum of 7 nested
functions) One could use:
B1:
=SUBSTITUTE(A1,"_",".",MAX(LEN(A1)-LEN(SUBSTITUTE(A1,
"_","")),1))
...

Two formulas not needed. You seem to have posted this before I sent my
latest prior follow-up.

This works correctly for the following test data:
...
ABC,DEF123,IJK6,2.xls
...

Testing is good, but it should be realistic. These are filenames, and I
believe it's uncommon for Mac files to carry extensions, so these are
likely Windows filenames. If so, commas aren't valid characters in
filenames.


---
Message posted from http://www.ExcelForum.com/

harlan:

incognito with 'hgrove'. what gives?

frank:

thanks for the perseverence.

i will try both the udfs and the formulae. please bear with me till i
test it out and get back to you.

thanks,

mac.


---
Message posted from http://www.ExcelForum.com/

Frank Kabel wrote...
..
Two formulas not needed. You seem to have posted this before I sent
my
latest prior follow-up.

Correct. But I also posted a combined formula :-) Though in the last
one I forgot to add and "_" & part. But as this is now mainly fun (at
least for me) this is probably not that important.
Also saw your formula and added a comment regarding version numbers
like _6_2.xls.



This works correctly for the following test data:
..
ABC,DEF123,IJK6,2.xls
Testing is good, but it should be realistic. These are filenames, and
I believe it's uncommon for Mac files to carry extensions, so these
are likely Windows filenames. If so, commas aren't valid characters
in
filenames.

Agreed (not a realistic one. Was just one to check for strings without
'_'. But definelty not a valid filename

Frank

Frank Kabel wrote...
...
. . . Though this won't handle:
ABC_DEF123_IJK_6_2.xls

returns 38023.

Looks like you're testing my formula on a machine with non-US default
date formats since 38023 is 6-Feb-2004 under the 1900 date system. My
formula returns 6.2 for this on my machine with standard US settings.

Unless the OP surprises me, I'm not going to worry much about this
failing internationalization. For that matter, does this imply that
Excel is stupid enough to convert "6.2" to a date rather than 6 + 2/10
?


---
Message posted from http://www.ExcelForum.com/

Frank Kabel wrote...
..
. . . Though this won't handle:
ABC_DEF123_IJK_6_2.xls

returns 38023.

Looks like you're testing my formula on a machine with non-US default
date formats since 38023 is 6-Feb-2004 under the 1900 date system. My
formula returns 6.2 for this on my machine with standard US settings.

Unless the OP surprises me, I'm not going to worry much about this
failing internationalization. For that matter, does this imply that
Excel is stupid enough to convert "6.2" to a date rather than 6 +
2/10
?

Hi Harlan
correct guess. changing the regional settings for the decimal delimiter
corrects this (and I agree one should not worry about this).

Also correct that Excel converts '6.2' to a date

Frank