Adding numbers in multiple cells where individual cells contain both numbers and text

**gamurt** · 01-08-2014, 07:39 PM

I have a column that looks like the following and I need to add the numbers:

27 skids
31 skids
56 skids
13 skids

The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"

Thank you.

**TMS** · 01-08-2014, 07:46 PM

Assuming that data is in cells A2:A5, then use:

Formula:

Please Login or Register  to view this content.

HTML Code:

Regards, TMS

**gamurt** · 01-08-2014, 08:50 PM

Thanks TM. I pasted that into my spreadsheet and it works. One other question: every now and then that same column will contain cells with text but no numbers. Is there a way to ignore those cells so I don't get an error #value! ?

**Tony Valko** · 01-08-2014, 10:50 PM

Try this...

Data Range

	A	B	C
2	27 skids	-----	127
3	31 skids
4	text
5	56 skids
6	13 skids
7
8
9
10

This array formula** entered in C2:

=SUM(IF(ISNUMBER(-LEFT(A2:A10)),--LEFT(A2:A10,FIND(" ",A2:A10))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Adding numbers in multiple cells where individual cells contain both numbers and text

LinkBack

Thread Tools

Rate This Thread

Display

Adding numbers in multiple cells where individual cells contain both numbers and text

Re: Adding numbers in multiple cells where individual cells contain both numbers and text

Re: Adding numbers in multiple cells where individual cells contain both numbers and text

Re: Adding numbers in multiple cells where individual cells contain both numbers and text

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] adding two or more cells with text and numbers

Adding cells that contain numbers and text

[SOLVED] adding cells which include numbers and text

adding cells after stripping numbers out of text fields

adding cells with text and numbers

Bookmarks

Bookmarks

Posting Permissions