Sum number(s) from part of cell value

[WAULKMILLFH]

Learned people I have a spreadsheet of data downloaded from an order form in Jotform. In certain cells there is a value such as Tomato Red Alert [1]

What I would like to do is 'Sum' the numeric values from each cell at the bottom of the column

I have deleted the [] and then used the RIGHT function as an experiment/test but I would have to do this in almost every column which would be very time consuming

many thanks in advance for your kind assistance

Peter

[AliGW]

Try this (untested):

=SUM(IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(A1:A5000,"*[",""),"]","")),""))

[Kaper]

One of possible approaches would be using Text to columns (found on Data ribbon in Data tools group). Select column, choose separated values set [ as separator and mark first column as "not import". Then either do the same for ] or just Find&replace ] with nothing

[WAULKMILLFH]

Hi Ali that returns 0....thank you for your reply

[WAULKMILLFH]

Many thanks will give that a try

[Kaper]

As for formula solution (tested) try:

Please Login or Register  to view this content.

[WAULKMILLFH]

Thank you will try that a little later

[WAULKMILLFH]

As for formula solution (tested) try:

Please Login or Register  to view this content.

Ok given that a try works for one column but not if I copy it to other columns am attaching an extract of the full spreadsheet and also a screendump with an error message for your kind attentionplantsalesformuale.jpg

[Glenn Kennedy]

Yes it will. read the yellow banner, top of page.

[WAULKMILLFH]

Many thanks Glenn!!

[Glenn Kennedy]

One way (of many!!):


=SUM(IFERROR(-MID(SUBSTITUTE(SUBSTITUTE(B2:B29,"[","("),"]",")"),FIND("(",SUBSTITUTE(SUBSTITUTE(B2:B29,"[","("),"]",")")),20),0))

[WAULKMILLFH]

Glenn you are a star man!! Thank you so much works perfectly

[Kaper]

If you want to use it in the same column where it is written you cvannot reference whole column, so in A33 for instance:

Please Login or Register  to view this content.

and copy right.

As a side effect - it _could_ work somewhat quicker than whole-columns referrencing formula

[WAULKMILLFH]

Yes thank you very much that works fine also!!

[Kaper]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[bebo021999]

Alternative formula, but non-array, normally Enter:

B33, add up to 6 digit number inside []:

Please Login or Register  to view this content.

[WAULKMILLFH]

Excellent many thanks!!