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 |
#1
|
|||
|
|||
finding a value in a string
i am looking for some help in finding a particular value in a string.
i have strings that look like: ABC_DEF123_IJK_V(1).xls or ABC_DEF123_IJK_V2.xls or ABC_DEF123_IJK_V3_A.xls or ABC_DEF123_IJK_V4_NA.xls or ABC_DEF123_IJK_V5.0.xls or ABC_DEF123_IJK_6_0.xls or ABC DEF123 IJK V7.xls these are the filenames of various files that users would have created and made an entry into this one master sheet along a column. i have as many nomenclatures as i have users. i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they denote a certain value that is of importance to other calculations. now, i have created upto 5 columns with RIGHT, MID, LEFT and WHAT HAVE YOU and WHAT NOT to extricate the values from the strings. i have over 3000 rows from which to extract data. i have even combined upto 3 IF's to get the desired output in some cases. only problem is, i have more variety than the number of IFs allowed (7) incorporating ISNUMBER, VALUE and ISTEXT functions. actually, after the 3rd IF, the formula has more number of braces than characters!!! i am not able to create a single formula that would take care of all the variety i have. over 5-6 columns, and even after that, a little bit of manual tweaking, allows me to get the right value. however, it is quite cumbersome. would anyone be able to give me one formula that could pick that number for me from the string? there is one commonality in those strings. the number that i require to be picked up resides within 5 places to the left of the ".xls". thanks in advance, mac. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
finding a value in a string
On Tue, 13 Jul 2004 13:10:24 -0500, icestationzbra
wrote: would anyone be able to give me one formula that could pick that number for me from the string? It's not clear from your posting what kind of output you want. But here is a UDF (User defined function) that extracts all of the digits as a number. If you have a specific output in mind, please post it. ================================= Function GetValue(str) Dim N As Integer, i As String i = "" For N = 1 To Len(str) If IsNumeric(Mid(str, N, 1)) Then i = i & Mid(str, N, 1) End If Next If i = "" Then GetValue = i Exit Function End If GetValue = CDbl(i) End Function ======================== To enter this, alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer, then Insert/Module and paste the code into the window that opens. Use it in your worksheet like any function. e.g. =getvalue(A1) --ron |
#3
|
|||
|
|||
finding a value in a string
Hi
try the following UDF: Public Function Get_Numerics(rng As Range) As Double Dim i As Integer Dim res Dim sValue sValue = Mid(rng.Value, Len(rng.Value) - 9, 5) For i = 1 To Len(sValue) If IsNumeric(Mid(sValue, i, 1)) Then res = res & Mid(sValue, i, 1) End If Next i res = CDbl(res) Get_Numerics = res End Function and use it like =GET_NUMERICS(A1) -- Regards Frank Kabel Frankfurt, Germany i am looking for some help in finding a particular value in a string. i have strings that look like: ABC_DEF123_IJK_V(1).xls or ABC_DEF123_IJK_V2.xls or ABC_DEF123_IJK_V3_A.xls or ABC_DEF123_IJK_V4_NA.xls or ABC_DEF123_IJK_V5.0.xls or ABC_DEF123_IJK_6_0.xls or ABC DEF123 IJK V7.xls these are the filenames of various files that users would have created and made an entry into this one master sheet along a column. i have as many nomenclatures as i have users. i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they denote a certain value that is of importance to other calculations. now, i have created upto 5 columns with RIGHT, MID, LEFT and WHAT HAVE YOU and WHAT NOT to extricate the values from the strings. i have over 3000 rows from which to extract data. i have even combined upto 3 IF's to get the desired output in some cases. only problem is, i have more variety than the number of IFs allowed (7) incorporating ISNUMBER, VALUE and ISTEXT functions. actually, after the 3rd IF, the formula has more number of braces than characters!!! i am not able to create a single formula that would take care of all the variety i have. over 5-6 columns, and even after that, a little bit of manual tweaking, allows me to get the right value. however, it is quite cumbersome. would anyone be able to give me one formula that could pick that number for me from the string? there is one commonality in those strings. the number that i require to be picked up resides within 5 places to the left of the ".xls". thanks in advance, mac. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
finding a value in a string
sorry if i was not clear in my earlier posting.
if you see the filenames that i have mentioned, there is a number within the last 5-6 characters. thats the only number that keeps changing (1, 2, 3, 4, 5, 6, 7) within all the filenames that i have given. thats a sort of version number and thats what i am looking to extract. for example, in "ABC DEF123 IJK V7.xls", '7' is what i am looking to extract. in "ABC_DEF123_IJK_V5.0.xls", '5' is what i am looking to extract. if the UDF extracts everything from the string, i might still have to do some drudgery. some filenames are as long as 100 characters with several numbers in them. however, there is one condition that almost 95% of the strings would adhere to. to the left of ".xls", if the first numeric character that is greater than zero could be picked, that would be the number i am looking for. please help. --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
finding a value in a string
Hi
have you tried the UDF?. It should return only this version number. At least it worked for your example data? If not what problems did you encounter (please post the example filename in these cases) -- Regards Frank Kabel Frankfurt, Germany sorry if i was not clear in my earlier posting. if you see the filenames that i have mentioned, there is a number within the last 5-6 characters. thats the only number that keeps changing (1, 2, 3, 4, 5, 6, 7) within all the filenames that i have given. thats a sort of version number and thats what i am looking to extract. for example, in "ABC DEF123 IJK V7.xls", '7' is what i am looking to extract. in "ABC_DEF123_IJK_V5.0.xls", '5' is what i am looking to extract. if the UDF extracts everything from the string, i might still have to do some drudgery. some filenames are as long as 100 characters with several numbers in them. however, there is one condition that almost 95% of the strings would adhere to. to the left of ".xls", if the first numeric character that is greater than zero could be picked, that would be the number i am looking for. please help. --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
finding a value in a string
frank,
i had tried your udf. here are two strings that i tested it for, wherein it failed. ABC_DEF_IJK_MNO070_UTHI05_1.1_V1.xls the udf returned 11, the expected output is 1. ABC_DEF_IJK_MNO120_QTLY19BREP_1.xls the udf returned 0, the expected output is 1. there are other cases wherein it gave the correct output. thank you for your response and help. mac. --- Message posted from http://www.ExcelForum.com/ |
#7
|
|||
|
|||
finding a value in a string
Hi
in the UDF change the line sValue = Mid(rng.Value, Len(rng.Value) - 9, 5) to sValue = Mid(rng.Value, Len(rng.Value) - 8, 5) This solves your second example. The first example is difficult as you said that the version number is within the last 5 characters before '.xls'. But in this example there's also a part of a previous number within this range. To solve also this problem try the following UDF: Public Function Get_Numerics(rng As Range) As Double Dim i As Integer Dim res Dim sValue Dim version version = False sValue = Mid(rng.Value, Len(rng.Value) - 8, 5) For i = Len(sValue) To 1 Step -1 If IsNumeric(Mid(sValue, i, 1)) Then version = i res = Mid(sValue, version, 1) While IsNumeric("0" & Mid(sValue, version - 1, 1) & res) version = version - 1 res = Mid(sValue, version, 1) & res Wend Exit For End If Next i res = CDbl(res) Get_Numerics = res End Function Not fully tested but give it a try -- Regards Frank Kabel Frankfurt, Germany frank, i had tried your udf. here are two strings that i tested it for, wherein it failed. ABC_DEF_IJK_MNO070_UTHI05_1.1_V1.xls the udf returned 11, the expected output is 1. ABC_DEF_IJK_MNO120_QTLY19BREP_1.xls the udf returned 0, the expected output is 1. there are other cases wherein it gave the correct output. thank you for your response and help. mac. --- Message posted from http://www.ExcelForum.com/ |
#8
|
|||
|
|||
finding a value in a string
Frank Kabel wrote...
have you tried the UDF?. It should return only this version number. At least it worked for your example data? ... *YOU* didn't test your UDF on the filenames ABC_DEF123_IJK_V2.xls or ABC DEF123 IJK V7.xls for which your UDF returns 0. Why? Classic off-by-one indexing error. The statement sValue = Mid(rng.Value, Len(rng.Value) - 9, 5) would need to be replaced with sValue = Mid(rng.Value, Len(rng.Value) - 8, 5) in order to pick up version numbers immediately preceding '.xls', but that exposes another failing of your UDF. If the change above were made, then for the filenames ABC_DEF123_IJK_V5.0.xls and ABC_DEF123_IJK_6_0.xls it returns 50 and 60, respectively, because you're using a character at a time algorithm that's clueless about '.' being a valid numeric character as long as it appears once with at least one adjacent numeral. Even then it'd choke on '6_0', which appears to be a valid alternative to 6.0. Anyway, a UDF that copes with all the OP's variations given so far needs to locate the rightmost numeric substring, and that requires at least two loops - first to find the end of the rightmost numeric substring, then to find it's beginning. Here's an alternative UDF that's more permissive with its argument and returns a string rather than a number, so strings without such numeric substrings return "" rather than #VALUE!. Public Function vn(ByVal v As Variant) As String Dim i As Long, n As Long, s As String, t As String n = Len(v) - 4 If n = 0 Then Exit Function 'v is too short For i = 0 To 4 If Mid(v, n - i, 1) Like "[0-9]" Then v = Mid(v, n - 4, 5 - i) n = 5 - i Exit For End If Next i If i 4 Then Exit Function For i = n - 1 To 0 Step -1 s = Right(v, n - i) t = Application.WorksheetFunction.Substitute(s, "_", ".") If IsNumeric(s) Then vn = s ElseIf IsNumeric(t) Then vn = t Else Exit For End If Next i End Function --- Message posted from http://www.ExcelForum.com/ |
#9
|
|||
|
|||
finding a value in a string
icestationzbra wrote...
... i have strings that look like: 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.0.xls ... ABC_DEF123_IJK_6_0.xls ... ABC DEF123 IJK V7.xls these are the filenames of various files that users would have created and made an entry into this one master sheet along a column. i have as many nomenclatures as i have users. i need to extract the (1, 2, 3, 4, 5, 6, 7) from the strings. they denote a certain value that is of importance to other calculations. ... would anyone be able to give me one formula that could pick that number for me from the string? there is one commonality in those strings. the number that i require to be picked up resides within 5 places to the left of the ".xls". 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. --- Message posted from http://www.ExcelForum.com/ |
#10
|
|||
|
|||
finding a value in a string
Frank Kabel wrote...
have you tried the UDF?. It should return only this version number. At least it worked for your example data? .. *YOU* didn't test your UDF on the filenames Ack, got me :-) [....] Anyway, a UDF that copes with all the OP's variations given so far needs to locate the rightmost numeric substring, and that requires at least two loops - first to find the end of the rightmost numeric substring, then to find it's beginning. Here's an alternative UDF that's more permissive with its argument and returns a string rather than a number, so strings without such numeric substrings return "" rather than #VALUE!. like your approach. And your UDF also covers 5_5 (what my seond UDF does not) 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 10:52 PM |