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

[abdulwahedism]

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

 

[Eric W]

Welcome to the MrExcel forums!

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

Book5
	A	B	C
1	189.01Naira		Sum
2	001.20Naira		211.22
3	021.01Naira
Sheet5

Cell Formulas
Range	Formula
C2	C2	=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]

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]

In that case, try:

Book5
	A	B	C
1	0.00Naira		Sum
2	8.71Naira		88.55
3	6.66Naira
4	15.36Naira
5	27.87Naira
6	1.02Naira
7	28.93Naira
Sheet5

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(SUBSTITUTE("0"&A1:A10,"Naira","")+0)

 

[abdulwahedism]

Thanks it worked out