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 |
#21
|
|||
|
|||
finding a value in a string
"Frank Kabel" wrote...
.... Also correct that Excel converts '6.2' to a date Which, upon reflection, is probably appropriate since '6,2' would be 6 + 2/10 in such locales. |
#22
|
|||
|
|||
finding a value in a string
can you please tell me which is the latest formula or UDF that i am
supposed to be using :-)... --- Message posted from http://www.ExcelForum.com/ |
#23
|
|||
|
|||
finding a value in a string
lol
now you have many possibilities. I'd suggest to use Harlan's latest UDF or Harlan's latest post. Just try them. They should work at least for over 95% of your data (the rest then has to be corrected manually) -- Regards Frank Kabel Frankfurt, Germany can you please tell me which is the latest formula or UDF that i am supposed to be using :-)... --- Message posted from http://www.ExcelForum.com/ |
#24
|
|||
|
|||
finding a value in a string
i tried the UDF provided by harlan, as well as the formula provided by
frank. here are a few cases where they did not return the expected output (which have been provided in braces). the number after '=' are the actual outputs. ABC_DEF_IJK_MNO_P04_1.xls = 04.1 (1) ABC_DEF_IJK_Draft1a.1.xls = .1 (1) ABC_DEF_TE020_OLM__Draft1B_V1-1.xls = -1 (1) most of the correct outputs are in the format '1.0' or '2.0'. i have no issues with these. some also had a leading space, such as ' 1'. frank's formula: =LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5)))) ABC_DEF_CV070_ARC_1.0_v1.xls = 0 (1) ABC_DEF_AP130_1.2_v1.xls = 2 (1) ABC_DEF_ARC_1.3_V2.xls = 3 (2) ABC_DEF_MD070_v1.0_V1.xls = 0 (1) this was a good idea: ABC_DEF_TE020_CAPS_1_V().xls = #N/A mac. --- Message posted from http://www.ExcelForum.com/ |
#25
|
|||
|
|||
finding a value in a string
icestationzbra wrote...
can you please tell me which is the latest formula or UDF that i am supposed to be using You're supposed to try them all and see what works best. Actually, if you're going to use a UDF, then I should have mentioned the Subst function. See the following link for its code. http://www.google.com/groups?selm=7c...wsrange r.com You could use it as =subst(A1,".+[^._0-9][._]?(\d([._]\d+)?).*\.xls","$1") to extract the version number from the filenames. However, if you want to avoid UDFs (not a bad idea), looks like my last formula works in a single cell as long as you're using standard US settings. =LOOKUP(99999, ABS(-MID(SUBSTITUTE(LEFT(RIGHT(A1,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/ |
#26
|
|||
|
|||
finding a value in a string
Hi
change the formula =LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN (SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5)))) to =LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE("_" & A1,"_",".",LEN("_"&A1)-LEN(SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/ 5),1+MOD(seq,5)))) -- Regards Frank Kabel Frankfurt, Germany i tried the UDF provided by harlan, as well as the formula provided by frank. here are a few cases where they did not return the expected output (which have been provided in braces). the number after '=' are the actual outputs. ABC_DEF_IJK_MNO_P04_1.xls = 04.1 (1) ABC_DEF_IJK_Draft1a.1.xls = .1 (1) ABC_DEF_TE020_OLM__Draft1B_V1-1.xls = -1 (1) most of the correct outputs are in the format '1.0' or '2.0'. i have no issues with these. some also had a leading space, such as ' 1'. frank's formula: =LOOKUP(99999,ABS(-MID(LEFT(RIGHT(SUBSTITUTE(A1,"_",".",LEN("_"&A1)-LEN (SUBSTITUTE("_"&A1,"_",""))),9),5),6-INT(seq/5),1+MOD(seq,5)))) ABC_DEF_CV070_ARC_1.0_v1.xls = 0 (1) ABC_DEF_AP130_1.2_v1.xls = 2 (1) ABC_DEF_ARC_1.3_V2.xls = 3 (2) ABC_DEF_MD070_v1.0_V1.xls = 0 (1) this was a good idea: ABC_DEF_TE020_CAPS_1_V().xls = #N/A mac. --- Message posted from http://www.ExcelForum.com/ |
#27
|
|||
|
|||
finding a value in a string
harlan,
the latest formula provided by you, gives a #N/A for everything. where should i use the subst function in the UDF? mac. --- Message posted from http://www.ExcelForum.com/ |
#28
|
|||
|
|||
finding a value in a string
icestationzbra wrote...
i tried the UDF provided by harlan, as well as the formula provided by frank. here are a few cases where they did not return the expected output (which have been provided in braces). the number after '=' are the actual outputs. ... See my most recent previous response in which I mentioned the Subst function. It's beginning to look like it's the only thing that would work, and you might need to change the formula to =subst(LEFT(RIGHT(O1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1") this was a good idea: ABC_DEF_TE020_CAPS_1_V().xls = #N/A But you've also written that you have filenames like ABC_DEF123_IJK_V4_NA.xls for which 4 is presumably the result you seek. If you want to look for the first numeric substring to the right of '_V' when present, then the Subst function could accomodate that, but that begs the question how to handle ABC_DEF123_IJK_V5_V.xls ? There does come a point at which if enough humans are allowed to throw enough random character variations at a computer, the computer won't be able to detect any pattern. --- Message posted from http://www.ExcelForum.com/ |
#29
|
|||
|
|||
finding a value in a string
frank,
that formula results in '0.1' whenever there is a '_1' in the input string. mac. --- Message posted from http://www.ExcelForum.com/ |
#30
|
|||
|
|||
finding a value in a string
[....]
ABC_DEF123_IJK_V5_V.xls ? There does come a point at which if enough humans are allowed to throw enough random character variations at a computer, the computer won't be able to detect any pattern. Ack sometimes it's just not possible to parse such differing strings :-) For the OP: For the future you may consider defining some name conventions for your filenames (and maybe even restrict them). As Harlan said there's a point completely free user entries will mess with any type of formula/code :-) 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 |