Google Sheets: Need to total numbers contained in the same cells as text.

[Matthew Sid]

I've been searching and trying different formulas for hours and got nowhere. I need to total the numbers in a row that contains both text and numbers excluding black cells
Example: B4 = "2 Dave", B5="4 Steve"
I want that to show a total of 6.

Tried SUMIF and SUMPRODUCT with ISNUMBER, but just getting errors.

Any help is appreciated. Thanks

 

[jtakw]

Hi,

If the Text strings Always start with the number followed by space, try:

Book3.xlsx
	B	C	D
3			Result
4	2 Dave		19
5	4 Steve
6	13 John
Sheet982

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(LEFT(B4:B6,FIND(" ",B4:B6))+0)

 

[Peter_SSs]

contains both text and numbers excluding black cells

If those were meant to be blank cells then you might need an adjustment to @jtakw's formula. For example:

22 02 03.xlsm
	B	C	D
3			Result
4	2 Dave		19
5	4 Steve
6
7	13 John
Sum

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(LEFT(B4:B7&0,FIND(" ",B4:B7&" "))+0)

 

[jtakw]

Thanks @Peter_SSs , missed the "black" > blank cells part.

Here's another way just in case there are also cells that are text only:

Book3.xlsx
	B	C	D
3			Result
4	2 Dave		19
5	4 Steve
6	abc
7
8	13 John
Sheet982

Cell Formulas
Range	Formula
D4	D4	=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8)),0)+0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Matthew Sid]

Thanks @Peter_SSs , missed the "black" > blank cells part.

Here's another way just in case there are also cells that are text only:

Book3.xlsx
	B	C	D
3			Result
4	2 Dave		19
5	4 Steve
6	abc
7
8	13 John
Sheet982

Cell Formulas
Range	Formula
D4	D4	=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8)),0)+0)
Press CTRL+SHIFT+ENTER to enter array formulas.

[/R

Cell Formulas
Range	Formula
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks @Peter_SSs , missed the "black" > blank cells part.

Here's another way just in case there are also cells that are text only:

Book3.xlsx
	B	C	D
3			Result
4	2 Dave		19
5	4 Steve
6	abc
7
8	13 John
Sheet982

Cell Formulas
Range	Formula
D4	D4	=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8)),0)+0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks. So tried all the suggested formulas in Excel and all worked. However, forgot to mention that the actual document is a Google Sheet, and the last formula

 

[Matthew Sid]

Thanks. So tried all the suggested formulas in Excel and all worked. However, forgot to mention that the actual document is a Google Sheet, and the last formula (IFERROR) was the only one that worked in the first two cells, but then got a #VALUE error in subsequent cells as follows.

"Function ADD parameter 1 expects number values. But "J" is text and cannot be coerced to a number"

Tried to format the cell as a number, but no luck.

Sorry, offered to help a friend and not that familiar with Google Sheets.

 

[Peter_SSs]

the actual document is a Google Sheet

In that case the question should not be asked in the Excel Questions forum

I have moved it

 

[Peter_SSs]

Here's another way just in case there are also cells that are text only:

I know this is no longer an Excel question, but I think you have your IFERROR final argument in the wrong place as it still fails if the text entry contains any space characters.
Should be this?

=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8))+0,0))

 

[jtakw]

I know this is no longer an Excel question, but I think you have your IFERROR final argument in the wrong place as it still fails if the text entry contains any space characters.
Should be this?

=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8))+0,0))

Seems to work either way, number conversion 1st, error convert to 0 (as you suggest), or
error convert to 0, then convert to number (as you see in my Post # 4 above)

EDIT: I see what you mean, leading and/or trailing spaces, or multiple text (words).

 

[Matthew Sid]

I know this is no longer an Excel question, but I think you have your IFERROR final argument in the wrong place as it still fails if the text entry contains any space characters.
Should be this?

=SUM(IFERROR(LEFT(B4:B8,FIND(" ",B4:B8))+0,0))

Thank you! That worked. And apologies for posting in the wrong thread.