Finding the Sum while ignoring text

[TeNW81]

I have an excel spreadsheet where I imported the data from another program and the cells contain text. When i try to use the sum function I get O. Any suggestions? I was under the impression that the sum function ignored text. See below
Thanks in advance

23.96 ft
79.84 ft
23.86 ft
30.17 ft
34.23 ft
31.62 ft
83.03 ft
23.48 ft
166.02 ft
55.49 ft
31.23 ft
31.03 ft
=SUM(C30:C41)

I get Zero.

 

[VoG]

With a helper column

Excel Workbook
	A	B
1	23.96 ft	23.96
2	79.84 ft	79.84
3	23.86 ft	23.86
4	30.17 ft	30.17
5	34.23 ft	34.23
6	31.62 ft	31.62
7	83.03 ft	83.03
8	23.48 ft	23.48
9	166.02 ft	166.02
10	55.49 ft	55.49
11	31.23 ft	31.23
12	31.03 ft	31.03
13		613.96
Sheet3

 

[TeNW81]

Vog,
Thanks for the reply. When entering the code shown below I recieved a #value! error. Any ideas?
Thanks.

 

[VoG]

Is your data actually in column A. If not you'll need to adjust the formula.

 

[TeNW81]

VoG,
My data is located where the formula references. Do you think that because the data is imported, it is not working like a normal data entry? I don't understand why it is not working. Thanks for the help.

 

[VoG]

Select one of the spaces between the number and ft, copy it and paste into a spare cell (say Z1)/ What does this return

=CODE(Z1)

 

[Aladin Akyurek]

Does...

=SUMPRODUCT(LEFT(C30:C41,FIND(" ",C30:C41)-1)+0)

work?

 

[T. Valko]

I have an excel spreadsheet where I imported the data from another program and the cells contain text. When i try to use the sum function I get O. Any suggestions? I was under the impression that the sum function ignored text. See below
Thanks in advance

23.96 ft
79.84 ft
23.86 ft
30.17 ft
34.23 ft
31.62 ft
83.03 ft
23.48 ft
166.02 ft
55.49 ft
31.23 ft
31.03 ft
=SUM(C30:C41)

I get Zero.

Try this...

=SUMPRODUCT(--LEFT(C30:C41,LEN(C30:C41)-3))

Returns 613.96

 

[TeNW81]

T. Valk0- When I enter this formula I get a circular reference warning.
Aladin- This formula worked for me. I was not putting a space between the "" located in your formula or VoG's so that is why it wasn't working originally. Thank you.
Now how do i get the returned sum to display the ft label that the other cells contain?

 

[Aladin Akyurek]

T. Valk0- When I enter this formula I get a circular reference warning.
Aladin- This formula worked for me. I was not putting a space between the "" located in your formula or VoG's so that is why it wasn't working originally. Thank you.

Glad to hear that. Thanks for providing feedback.

Now how do i get the returned sum to display the ft label that the other cells contain?

Looks like you mean:

=SUMPRODUCT(LEFT(C30:C41,FIND(" ",C30:C41)-1)+0)&" ft"