Forum Discussion
SUM VALUE WITH TEXT HAVING BLANK SPACE
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 Replies
hi
=SUM(IFERROR(TEXTBEFORE(A1:A5," ")*1,0))
or
=SUM(IFERROR(LEFT(A1:A5,FIND(" ",A1:A5)-1)*1,0))
=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.
