If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
finding a value in a string
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/ |
#13
|
|||
|
|||
finding a value in a string
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 |
#14
|
|||
|
|||
finding a value in a string
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 |
#15
|
|||
|
|||
finding a value in a string
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 |
#16
|
|||
|
|||
finding a value in a string
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/ |
#17
|
|||
|
|||
finding a value in a string
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/ |
#18
|
|||
|
|||
finding a value in a string
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 |
#19
|
|||
|
|||
finding a value in a string
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/ |
#20
|
|||
|
|||
finding a value in a string
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question re MailMerge and VB.NET | thecoiman | Mailmerge | 5 | May 17th, 2004 04:13 PM |
finding certain characters within a string within multiple cells | Gav !! | Worksheet Functions | 1 | April 15th, 2004 08:27 AM |
Inserting a space into a text string | Brian Anderson | Worksheet Functions | 1 | April 6th, 2004 05:39 AM |
Finding last name in a first last name string | Brian Bonner | Worksheet Functions | 4 | March 31st, 2004 09:41 PM |
Finding a text string within a cell | Peo Sjoblom | Worksheet Functions | 6 | February 16th, 2004 09:52 PM |