I want to sum only the numbers in a column of cells that has numbers and text

abdulwahedism

New Member
Joined
Jun 10, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have a column that I want to sum only the numbers and ignore the texts, I tried alot of formulas but doesn't work. The column looks like below:

189.01Naira
001.20Naira
021.01Naira
Etc
Kindly help me out please
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,471
Welcome to the MrExcel forums!

Is the number always 6 digits? This seems likely since you have preceding zeros. If so, try:

Book5
ABC
1189.01NairaSum
2001.20Naira211.22
3021.01Naira
Sheet5
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(LEFT(A1:A3,6)+0)


If not, we need a bit more info. Is the text on the end always "Naira"? Always 5 digits?
 

abdulwahedism

New Member
Joined
Jun 10, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
The text always end with "Naira" while the number is always 3 or 4 digits such as:
0.00Naira
8.71Naira
6.66Naira
15.36Naira
27.87Naira
1.02Naira
28.93Naira
And so on

I really your quick reply
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,471
In that case, try:

Book5
ABC
10.00NairaSum
28.71Naira88.55
36.66Naira
415.36Naira
527.87Naira
61.02Naira
728.93Naira
Sheet5
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(SUBSTITUTE("0"&A1:A10,"Naira","")+0)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,351
Messages
5,571,677
Members
412,412
Latest member
NWPhotoExplorer
Top