Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
I have various cells some of which hold an alpha-number field, some hold an
alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
Hi,
How about a user defined function. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call the code with =addtime(rng,"L") where rng is the range to count and "L" is the letter to sum so for your example =addtime(C3:G3,"h") Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have various cells some of which hold an alpha-number field, some hold an alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
Hi Mike,
Wow!! That works perfectly. I have a follow on question though. I have a second formula that calculates if the "holiday" has been taken taking into account the current date (which is held in cell A2) and if it has passed my column header dates held in row 2 from C2 onwards. This formula is as follows: =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2))) How do I amend this to incorporate your =addtime(C3:CN3,"H") formula? Currently its giving me a VALUE error bcause of the merging in one cell of for example H3.5L4.5. Thanks. -- PK wilts "Mike H" wrote: Hi, How about a user defined function. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call the code with =addtime(rng,"L") where rng is the range to count and "L" is the letter to sum so for your example =addtime(C3:G3,"h") Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have various cells some of which hold an alpha-number field, some hold an alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
Hi,
The formula you posted won't work because the ranges are different lengths. I think your saying that if the date in the row above the string is 'in the future then ignore it. Try this Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If c.Offset(-1, 0) Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If getmeout: Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Hi Mike, Wow!! That works perfectly. I have a follow on question though. I have a second formula that calculates if the "holiday" has been taken taking into account the current date (which is held in cell A2) and if it has passed my column header dates held in row 2 from C2 onwards. This formula is as follows: =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2))) How do I amend this to incorporate your =addtime(C3:CN3,"H") formula? Currently its giving me a VALUE error bcause of the merging in one cell of for example H3.5L4.5. Thanks. -- PK wilts "Mike H" wrote: Hi, How about a user defined function. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call the code with =addtime(rng,"L") where rng is the range to count and "L" is the letter to sum so for your example =addtime(C3:G3,"h") Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have various cells some of which hold an alpha-number field, some hold an alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
Ah,
I understand what your saying. Your validation date is 'always' in row 2 but the holiday coluld be in any row so if we are in row 9 we still colpare the date in row 2 to the date in a2. Try this instead:- Function addtime(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If getmeout: Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, The formula you posted won't work because the ranges are different lengths. I think your saying that if the date in the row above the string is 'in the future then ignore it. Try this Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If c.Offset(-1, 0) Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If getmeout: Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Hi Mike, Wow!! That works perfectly. I have a follow on question though. I have a second formula that calculates if the "holiday" has been taken taking into account the current date (which is held in cell A2) and if it has passed my column header dates held in row 2 from C2 onwards. This formula is as follows: =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2))) How do I amend this to incorporate your =addtime(C3:CN3,"H") formula? Currently its giving me a VALUE error bcause of the merging in one cell of for example H3.5L4.5. Thanks. -- PK wilts "Mike H" wrote: Hi, How about a user defined function. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call the code with =addtime(rng,"L") where rng is the range to count and "L" is the letter to sum so for your example =addtime(C3:G3,"h") Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have various cells some of which hold an alpha-number field, some hold an alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting elements of a string cell
This will sound dumb I know but does your second piece of VBA also accomodate
my first request? ie can I use this coding to calculate holiday booked (ie my first request) and holiday taken (ie my second request) in two separate result cells? Also if I need to replace the first piece of code you sent me how do I open the existing code within VB? I am a total novice in VB and as I say prob a dumb question but please let me know anyway :-) -- PK wilts "Mike H" wrote: Ah, I understand what your saying. Your validation date is 'always' in row 2 but the holiday coluld be in any row so if we are in row 9 we still colpare the date in row 2 to the date in a2. Try this instead:- Function addtime(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If getmeout: Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, The formula you posted won't work because the ranges are different lengths. I think your saying that if the date in the row above the string is 'in the future then ignore it. Try this Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If c.Offset(-1, 0) Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If getmeout: Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Hi Mike, Wow!! That works perfectly. I have a follow on question though. I have a second formula that calculates if the "holiday" has been taken taking into account the current date (which is held in cell A2) and if it has passed my column header dates held in row 2 from C2 onwards. This formula is as follows: =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2))) How do I amend this to incorporate your =addtime(C3:CN3,"H") formula? Currently its giving me a VALUE error bcause of the merging in one cell of for example H3.5L4.5. Thanks. -- PK wilts "Mike H" wrote: Hi, How about a user defined function. Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call the code with =addtime(rng,"L") where rng is the range to count and "L" is the letter to sum so for your example =addtime(C3:G3,"h") Function addtime(rng As Range, ltr As String) Dim ts As String Dim x As Long ltr = UCase(ltr) For Each c In rng If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtime = addtime + Val(ts) ts = "" End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have various cells some of which hold an alpha-number field, some hold an alpha-number-alpha-number field, some are blank. These range for example from cells C3:G3 as follows starting at C3 H7.5 blank H3.5L4.5 blank L7.5 I would like to sum all numbers prefixed with H and seprately, all prefixed with L Hence the result from above should read - Total for H is 11 Total for L is 12. I have 2 separate formulas as follows which work until you input an H value and an L value into the same field. =SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) =SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1)) I am aware this is an array formula and one must complete the shift control enter to obtain curved brackets around the formula. Any assistance to solve my issue would be very much appreciated. For info H = holiday taken. L = Lieu time taken. On some days the staff merge H with acquired L to make up a day off. Thanks, PK -- PK wilts |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Data from a string of text in a cell | Excel Worksheet Functions | |||
Extracting text from a string | Excel Worksheet Functions | |||
Extracting a string | Excel Discussion (Misc queries) | |||
Extracting from a text string | Excel Worksheet Functions | |||
Counting Elements in a String | Excel Worksheet Functions |