H
Harlan Grove
Frank Kabel said:
Which, upon reflection, is probably appropriate since '6,2' would be 6 +
2/10 in such locales.
Which, upon reflection, is probably appropriate since '6,2' would be 6 +
2/10 in such locales.
I
icestationzbra
can you please tell me which is the latest formula or UDF that i a
supposed to be using
..
supposed to be using
..
F
Frank Kabel
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)
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)
I
icestationzbra
i tried the UDF provided by harlan, as well as the formula provided b
frank. here are a few cases where they did not return the expecte
output (which have been provided in braces). the number after '=>' ar
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 n
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
frank. here are a few cases where they did not return the expecte
output (which have been provided in braces). the number after '=>' ar
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 n
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
H
hgrove
icestationzbra wrote...
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 mentione
the Subst function. See the following link for its code.
http://www.google.com/[email protected]
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 las
formula works in a single cell as long as you're using standard U
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)))
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 mentione
the Subst function. See the following link for its code.
http://www.google.com/[email protected]
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 las
formula works in a single cell as long as you're using standard U
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)))
F
Frank Kabel
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))))
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))))
I
icestationzbra
harlan,
the latest formula provided by you, gives a #N/A for everything.
where should i use the subst function in the UDF?
mac
the latest formula provided by you, gives a #N/A for everything.
where should i use the subst function in the UDF?
mac
H
hgrove
icestationzbra wrote...
See my most recent previous response in which I mentioned the Subs
function. It's beginning to look like it's the only thing that woul
work, and you might need to change the formula to
=subst(LEFT(RIGHT(O1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1")
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 fo
the first numeric substring to the right of '_V' when present, then th
Subst function could accomodate that, but that begs the question how t
handle
ABC_DEF123_IJK_V5_V.xls
?
There does come a point at which if enough humans are allowed to thro
enough random character variations at a computer, the computer won't b
able to detect any pattern
...
See my most recent previous response in which I mentioned the Subs
function. It's beginning to look like it's the only thing that woul
work, and you might need to change the formula to
=subst(LEFT(RIGHT(O1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1")
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 fo
the first numeric substring to the right of '_V' when present, then th
Subst function could accomodate that, but that begs the question how t
handle
ABC_DEF123_IJK_V5_V.xls
?
There does come a point at which if enough humans are allowed to thro
enough random character variations at a computer, the computer won't b
able to detect any pattern
I
icestationzbra
frank,
that formula results in '0.1' whenever there is a '_1' in the inpu
string.
mac
that formula results in '0.1' whenever there is a '_1' in the inpu
string.
mac
F
Frank Kabel
[....]
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
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
F
Frank Kabel
Hi
as mentioned in the previous post considering your differing types of
filenames I'd assume that every formula will have a success rate below
100% (but above lets say 95% -> esp. Harlan's subst UDF).
As in most cases with text parsing there will be some records you have
to parse/correct manually
as mentioned in the previous post considering your differing types of
filenames I'd assume that every formula will have a success rate below
100% (but above lets say 95% -> esp. Harlan's subst UDF).
As in most cases with text parsing there will be some records you have
to parse/correct manually
I
icestationzbra
frank and harlan,
thank you for all you perseverance.
i will make do with all this help that you have provided. most of th
cases are covered by the udf and formula. the rest, i think i can tak
care of them manually.
thanks once again,
mac
thank you for all you perseverance.
i will make do with all this help that you have provided. most of th
cases are covered by the udf and formula. the rest, i think i can tak
care of them manually.
thanks once again,
mac
H
Harlan Grove
icestationzbra > said:
Not on my system if you mean the latest LOOKUP formula. If the following
were in A1,
ABC_DEF123_IJK_V4_NA.xls
then the formula
=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))))
returns 4 on my system, and all but your latest pathological filenames
return correct results.
The Subst function *is* another UDF, so it's code goes into a general VBA
module, and you'd use it in the formula I gave in my previous response in
this branch. For filename in A1,
=subst(LEFT(RIGHT(A1,9),5),".*\D[._]?(\d([._]\d+)?).*","$1")
I
icestationzbra
harlan,
about the #N/A, i will check it again to see if i am doing somethin
wrong.
mac
about the #N/A, i will check it again to see if i am doing somethin
wrong.
mac
I
icestationzbra
hi,
i used a function to return all the numbers from a string. from that
picked up the rightmost string that is greater than zero. seems to b
working for me in some cases.
i am facing one problem though. if a string has no numeric characters
i get a #VALUE!. is there a way to get a message in there, instead? ca
a function return a message string ("No Number")?
thanks,
mac.
*****
Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
GetNum = CDbl(strTemp) 'convert string to long
End Functio
i used a function to return all the numbers from a string. from that
picked up the rightmost string that is greater than zero. seems to b
working for me in some cases.
i am facing one problem though. if a string has no numeric characters
i get a #VALUE!. is there a way to get a message in there, instead? ca
a function return a message string ("No Number")?
thanks,
mac.
*****
Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
GetNum = CDbl(strTemp) 'convert string to long
End Functio
M
Myrna Larson
Here's a modification.
BTW, your comment says "convert to long", but the function you used converts
to a double, in case that makes any difference.
Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
If Len(strTemp) = 0 Then
GetNum = "No digits found"
Else
GetNum = CDbl(strTemp) 'convert string to DOUBLE!!!
End If
End Function
BTW, your comment says "convert to long", but the function you used converts
to a double, in case that makes any difference.
Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
If Len(strTemp) = 0 Then
GetNum = "No digits found"
Else
GetNum = CDbl(strTemp) 'convert string to DOUBLE!!!
End If
End Function
I
icestationzbra
myrna,
thanks, that helped.
mac
thanks, that helped.
mac