SUM VALUE WITH TEXT HAVING BLANK SPACE

[JOGINDER SINGH]

Dear All.

Please solve this question.

I have value in Column A1 to Column A5 numbers and text mentioned below. But there is a blank column A2. I want to do sum skipping blank space therein. Please give me excel function.

34 ab

224 De

55 XY

4578 ZE

[#2]

@JOGINDER SINGH 

=SUMPRODUCT(IFERROR(NUMBERVALUE(LEFT(A1:A5,FIND(" ",A1:A5)-1)),0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

 

[#3]

hi

=SUM(IFERROR(TEXTBEFORE(A1:A5," ")*1,0))

or

=SUM(IFERROR(LEFT(A1:A5,FIND(" ",A1:A5)-1)*1,0))

@JOGINDER SINGH