Extracting & summing data from a single cell in excel

Question

I am trying to isolate some data contained within a cell in excel:

The idea is to add up all numbers before the "-" in the notes column. The data must all be contained within the same cell, as there are hundreds of lines on the full spreadsheet.

I.e. 4.3+0.8+4...

The amount of lines in the "Notes" cells will vary.

I tried using =TEXTBEFORE as below:

=TEXTBEFORE((NOTES CELL),"-",1)

and summing multiple TEXTBEFORE functions, but it becomes very messy with multiple lines inside the cell & needs to be adjusted based on how many lines are inside the cell.

I think I'm having an issue with the fact there is data inside the cell that I need to ignore (the 295### number).

Is this achievable?

Answer 1

Try using the following formula:

---

=SUM(--TEXTBEFORE(TEXTSPLIT(A1,CHAR(10)),"-"))

---

Or,

=SUM(--TAKE(TEXTSPLIT(A1,"-",CHAR(10)),,1))

---

Or as suggested by P.b Sir.

=SUM(TEXTSPLIT(A1,"-",CHAR(10))*{1,0})

---