| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values in string in a cell
A2 contain the following string :
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Dear Francis
Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Hi Jacob
Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
I assume the string can very in length. A formula would be difficult. If you
would really like to have the UDF amended to return the total numbers let me know... If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Hi Jacob Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Francis <xlsmate(AT)gmail(DOT)com wrote...
A2 contain the following string : ** CLAIMS ** *YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC *$30,708.72: YR2008 NO. 2 INC *$13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 *give me the total amount of 45590.68 Is there a way to do this? .... Like it or not, the best way to handle this sort of thing would be to parse such strings (records) into multiple columns (fields), then sum the numbers. You could parse and sum in single formulas, but it's nowhere near simple and very inefficient. Here's one way. Define a name like seq referring to the formula =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1)) Then if your string above were in a cell named x, use the array formula =SUM(--IF(seq<=(LEN(x)-LEN(SUBSTITUTE(x,"NO. ","")))/4, LEFT(MID(x,SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6), FIND(" ",MID(x&" ",SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6))))) to sum the numbers following the substring "NO. " in your full string/ record, and use the array formula =SUM(--IF(seq<=LEN(x)-LEN(SUBSTITUTE(x,"$","")), LEFT(MID(x,SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,1 2), FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq), seq)+1,12))-1))) to sum the amounts following the dollar signs ($). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Thanks. Agreed that a formula would be difficult in this case.
Appreciate if you can modify the UDF for total on NO. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: I assume the string can very in length. A formula would be difficult. If you would really like to have the UDF amended to return the total numbers let me know... If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Hi Jacob Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Here is my take on a UDF solution; the first UDF gives the sum of the "NO."
values and the second gives the sum of the money values... Function AddNo(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "NO.") For X = 0 To UBound(Parts) AddNo = AddNo + Val(Parts(X)) Next End Function Function AddMoney(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "No.") For X = 0 To UBound(Parts) AddMoney = AddMoney + Val(Replace(Split(Parts(X), "INC")(1), "$", "")) Next End Function -- Rick (MVP - Excel) "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... Thanks. Agreed that a formula would be difficult in this case. Appreciate if you can modify the UDF for total on NO. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: I assume the string can very in length. A formula would be difficult. If you would really like to have the UDF amended to return the total numbers let me know... If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Hi Jacob Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
On Fri, 8 May 2009 03:45:01 -0700, Francis <xlsmate(AT)gmail(DOT)com wrote:
A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA Harlan's answer, with regard to separate columns for the values, will be the easiest to maintain. If you must keep it together in a single string, you could download and install Longre's free morefunc.xll add-in (Google to find a good download site), and then use these **array-entered** formulas: Sum of NO: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT( A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+")) Sum of $: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT( A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+")) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Hi Rick
The 2nd UDF doesn't work as expected, it return the value for the first amount TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Rick Rothstein" wrote: Here is my take on a UDF solution; the first UDF gives the sum of the "NO." values and the second gives the sum of the money values... Function AddNo(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "NO.") For X = 0 To UBound(Parts) AddNo = AddNo + Val(Parts(X)) Next End Function Function AddMoney(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "No.") For X = 0 To UBound(Parts) AddMoney = AddMoney + Val(Replace(Split(Parts(X), "INC")(1), "$", "")) Next End Function -- Rick (MVP - Excel) "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... Thanks. Agreed that a formula would be difficult in this case. Appreciate if you can modify the UDF for total on NO. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: I assume the string can very in length. A formula would be difficult. If you would really like to have the UDF amended to return the total numbers let me know... If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Hi Jacob Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Hi
Thanks. Agreed that the best is to parse into multiple columns and then do the sum. The solutions provided works. I was thinking that formula can't handle these but you have proved me wrong. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Harlan Grove" wrote: Francis <xlsmate(AT)gmail(DOT)com wrote... A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? .... Like it or not, the best way to handle this sort of thing would be to parse such strings (records) into multiple columns (fields), then sum the numbers. You could parse and sum in single formulas, but it's nowhere near simple and very inefficient. Here's one way. Define a name like seq referring to the formula =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1)) Then if your string above were in a cell named x, use the array formula =SUM(--IF(seq<=(LEN(x)-LEN(SUBSTITUTE(x,"NO. ","")))/4, LEFT(MID(x,SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6), FIND(" ",MID(x&" ",SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6))))) to sum the numbers following the substring "NO. " in your full string/ record, and use the array formula =SUM(--IF(seq<=LEN(x)-LEN(SUBSTITUTE(x,"$","")), LEFT(MID(x,SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,1 2), FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq), seq)+1,12))-1))) to sum the amounts following the dollar signs ($). |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Sorry, my mistake, try these instead...
Function AddNo(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "NO.") For X = 0 To UBound(Parts) AddNo = AddNo + Val(Parts(X)) Next End Function Function AddMoney(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "INC") For X = 0 To UBound(Parts) AddMoney = AddMoney + Val(Replace(Replace(Parts(X), "$", ""), ",", "")) Next End Function -- Rick (MVP - Excel) "Francis" wrote in message ... Hi Rick The 2nd UDF doesn't work as expected, it return the value for the first amount TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Rick Rothstein" wrote: Here is my take on a UDF solution; the first UDF gives the sum of the "NO." values and the second gives the sum of the money values... Function AddNo(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "NO.") For X = 0 To UBound(Parts) AddNo = AddNo + Val(Parts(X)) Next End Function Function AddMoney(S As String) As Double Dim X As Long Dim Parts() As String Parts = Split(S, "No.") For X = 0 To UBound(Parts) AddMoney = AddMoney + Val(Replace(Split(Parts(X), "INC")(1), "$", "")) Next End Function -- Rick (MVP - Excel) "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... Thanks. Agreed that a formula would be difficult in this case. Appreciate if you can modify the UDF for total on NO. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: I assume the string can very in length. A formula would be difficult. If you would really like to have the UDF amended to return the total numbers let me know... If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: Hi Jacob Thanks! Your UDF does return the total for the amount. How do I modify for the NO. which will give me 5 in my example. Is there no formula that can do these? I am looking a formulas that can do this if there are. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jacob Skaria" wrote: Dear Francis Suppose you have the below data in cell A1 (as a single line) ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 Try this UDF which will give you the total.. =getvaluefromstring(A1) Launch VBE using Alt+F11. Insert a module and save.. Function GetValuefromString(varTemp As Range) As Currency Dim intStart As Integer Dim intPos As Integer Dim strData As String strData = varTemp.Text & ":" intStart = 1 Do intPos = InStr(intStart, strData, "INC") If intPos < 0 Then GetValuefromString = GetValuefromString + Trim(Mid(strData, _ intPos + 3, InStr(intPos, strData, ":") - intPos - 3)) End If intStart = intPos + 1 Loop While intPos < 0 End Function If this post helps click Yes --------------- Jacob Skaria "Francis" wrote: A2 contain the following string : ** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008 NO. 2 INC $13,922.14 I want in B2 to add up the No which will give 5 on the above example and in C2 give me the total amount of 45590.68 Is there a way to do this? TIA -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
Ron Rosenfeld wrote...
.... Sum of NO: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", FALSE),1),FALSE),"+")) I had wanted to avoid regexps, but if you're going to use 'em, use 'em efficiently. Either =SUMPRODUCT(--REGEX.MID(x,"(?<=NO\. )\d+",INTVECTOR(REGEX.COUNT(x,"NO \. "),1))) or =EVAL(REGEX.SUBSTITUTE(x&"NO. 0",".*?NO\. (\d+)","+[1]")) Sum of $: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", FALSE),1),FALSE),"+")) Either =SUMPRODUCT(--REGEX.MID(x&":","(?<=\$)[0-9,.]+(?=:)", INTVECTOR(REGEX.COUNT(x&":","\$[0-9,.]+:"),1))) or =EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(x&":",".*?\$ ([0-9,.]+):","+[1]"),",","")) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values in string in a cell
On Fri, 8 May 2009 14:10:59 -0700 (PDT), Harlan Grove
wrote: Ron Rosenfeld wrote... ... Sum of NO: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", FALSE),1),FALSE),"+")) I had wanted to avoid regexps, but if you're going to use 'em, use 'em efficiently. Either =SUMPRODUCT(--REGEX.MID(x,"(?<=NO\. )\d+",INTVECTOR(REGEX.COUNT(x,"NO \. "),1))) or =EVAL(REGEX.SUBSTITUTE(x&"NO. 0",".*?NO\. (\d+)","+[1]")) Sum of $: =EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b", INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b", FALSE),1),FALSE),"+")) Either =SUMPRODUCT(--REGEX.MID(x&":","(?<=\$)[0-9,.]+(?=:)", INTVECTOR(REGEX.COUNT(x&":","\$[0-9,.]+:"),1))) or =EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(x&":",".*?\$ ([0-9,.]+):","+[1]"),",","")) Much better. Thanks. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine which values are = 0 & create string | Excel Discussion (Misc queries) | |||
| changing text values to a different string | Excel Worksheet Functions | |||
| Sum delimited values in text string if... | Excel Worksheet Functions | |||
| Returning a string of values in an IF statement | Excel Discussion (Misc queries) | |||
| Extracting numeric values from string | Excel Worksheet Functions | |||
![Francis[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
