500g (Pkr 700) 500g (Pkr 1250) 500g (Pkr 900) 500g (Pkr 350) Pkr 1000 Pkr 750
This is the data from the Third row of the sheet . What formula should i use To extract values coming after Pkr and add all those extracted values.
For Instance. The Total Sum of these cell 500g (Pkr 700) 500g (Pkr 1250) 500g (Pkr 900) 500g (Pkr 350) Pkr 1000 Pkr 750 should be 4950
I tried this Formula
=SUMPRODUCT(--ISNUMBER(SEARCH("Pkr", B2:H2)), --MID(B2:H2, SEARCH("Pkr", B2:H2) + 3, LEN(B2:H2)))
And got wrong output
If you have Excel 365 you can use this formula:
=LET(d, B2:H2,
a,TEXTAFTER(d,"Pkr"),
b,TEXTBEFORE(a,")",,,,a),
SUM(--(b)))
It first retrieves the “text” after “Pkr” and then returns the value before the bracket (if present).
As text is returned, -- makes the values numeric.
Try this formula (array formula)
In cell K16:S16
500g (Pkr 700) 500g (Pkr 1250) 500g (Pkr 900) 500g (Pkr 350) Pkr 1000 Pkr 750
=SUM(IFERROR(--TEXTSPLIT(SUBSTITUTE(TEXTJOIN(" ",TRUE,K16:S16),")"," ")," ",,TRUE,FALSE),0))
I am not sure how is your data stored in the cell, as there is no proper screenshot nor any markdown data. However, one could try using one of the following:
=SUM(--TEXTBEFORE(TEXTAFTER(B2:G2,"Pkr ")&")",")"))
Or,
=SUM(SUBSTITUTE(TEXTAFTER(B2:G2,"Pkr "),")",)+0)
Or,
=SUMPRODUCT(SUBSTITUTE(RIGHT(B2:G2,LEN(B2:G2)-FIND("Pkr ",B2:G2)-2),")",)+0)
Older Versions, may need to hit CTRL+SHIFT+ENTER while exiting the edit mode for the above formula!
And here is a quick fix to your existing formulae :
=SUMPRODUCT(SUBSTITUTE(MID(B2:G2,FIND("Pkr ",B2:G2)+3,99),")",)+0)
My approach is here,
If the data is in individual cells..
=LET(ip,B3:G3,op,--TEXTSPLIT(ARRAYTOTEXT(SUBSTITUTE(SUBSTITUTE(ip,")","")," ","-"),0),{"-",","}),SUM(FILTER(op,NOT(ISERR(op)))))
Or
If the data is in Single cell..
=SUM(IFERROR(--SUBSTITUTE(TEXTSPLIT(SUBSTITUTE(O4," ","-"),"-"),")",""),0))
It’s never a good idea having multiple informations in one cell: replace “500g (Pkr 700)” in one cell by “500” in one cell, “g” in the next, “Pkr” again in the next and “700” in the last one 🙂
With your existing formula try this way:
=SUMPRODUCT(SUBSTITUTE(MID(B2:G2,FIND("Pkr ",B2:G2)+3,99),")",)+0)