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

#### abdulwahedism

##### New Member
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

### 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
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
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

#### Eric W

##### MrExcel MVP
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)

#### abdulwahedism

##### New Member
Thanks it worked out

Replies
1
Views
381
Replies
10
Views
127
Replies
3
Views
79
Replies
1
Views
125
Replies
3
Views
124