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 |
#31
|
|||
|
|||
finding a value in a string
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 -- Regards Frank Kabel Frankfurt, Germany frank, that formula results in '0.1' whenever there is a '_1' in the input string. mac. --- Message posted from http://www.ExcelForum.com/ |
#32
|
|||
|
|||
finding a value in a string
frank and harlan,
thank you for all you perseverance. i will make do with all this help that you have provided. most of the cases are covered by the udf and formula. the rest, i think i can take care of them manually. thanks once again, mac. --- Message posted from http://www.ExcelForum.com/ |
#33
|
|||
|
|||
finding a value in a string
"icestationzbra " wrote...
the latest formula provided by you, gives a #N/A for everything. 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. where should i use the subst function in the UDF? 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") |
#34
|
|||
|
|||
finding a value in a string
harlan,
about the #N/A, i will check it again to see if i am doing something wrong. mac. --- Message posted from http://www.ExcelForum.com/ |
#35
|
|||
|
|||
finding a value in a string
hi,
i used a function to return all the numbers from a string. from that i picked up the rightmost string that is greater than zero. seems to be 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? can 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 Function --- Message posted from http://www.ExcelForum.com/ |
#36
|
|||
|
|||
finding a value in a string
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 On Fri, 16 Jul 2004 15:28:46 -0500, icestationzbra wrote: hi, i used a function to return all the numbers from a string. from that i picked up the rightmost string that is greater than zero. seems to be 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? can 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 Function --- Message posted from http://www.ExcelForum.com/ |
#37
|
|||
|
|||
finding a value in a string
|
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 |