Counting help

[Guest]

I am trying to devise a formula which will enable me to count an array of
cells which contain text and numbers. I then want to ignore the text and
just add the numbers e.g. (cells spread over worksheet, containing LDS9
ES7.5 E L LD NS11 etc. The answer to this would be 27.5 (all th numbers
added together), is this possible?

 

[Bob Davison]

I am trying to devise a formula which will enable me to count an array of
cells which contain text and numbers. I then want to ignore the text and
just add the numbers e.g. (cells spread over worksheet, containing LDS9
ES7.5 E L LD NS11 etc. The answer to this would be 27.5 (all th numbers
added together), is this possible?

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Not sure what you mean by "count an array of cells which contain text and
numbers"...
Do you want to find only the cells which contain text and numbers when some
of the cells only contain text but no numbers?

To get rid of the text that is not a number try this:

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1),"",(LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,255)&"0123456789")),LEN(A1)),MIN(SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","
"},MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RIGHT(A1,255)&"0123456789")),LEN(A1))&"abcdefghijklmnopqrstuvwxyz
"))-1)*1))

The above formula looks into the string to see if it contains a number. If
it does, it then locates the position of the left-most number within the
string and now works with the string beginning with that number (this
eliminates the characters at the beginning that are not numbers). Then it
looks left-to-right to find the position of the first letter or space to the
right of that string. That result is used in the overall LEFT function to
determine how many charaters are returned. 1 is subtracted from the
position of the first letter or space so the letter or number itself is not
included in the result. The result is multiplied by 1 to convert the still
text result to a numerical result.

To get the total of the results, use a formula something like this which
will ignore any errors:

SUMIF(A1:A10,">=0")

Notes on the formula:

Assumes all positive numbers.
Assumes only numbers, letters, and spaces in strings.
No spaces in numbers or letters between numbers permitted (only the number
before a space or letter will be returned).
No multiple numbers (i.e. separated by non-numerical characters).
Up to 255 characters in string (may be increased if necessary)
Decimal numbers OK
A non-letter character to the right of and adjacent to a number will cause
an error (except for a decimal point).

Bob

 

[T. Valko]

Instead of stripping out all the letters so all that remains is the number,
just extract the number itself:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:255"))))

If no number is present or the cell is empty you'll get a #N/A error.

Biff

 

[Guest]

Thanks Bob,
I have tried the formula and it doesn't return any results, is it possible
to make it add the numbers in the cells and return the figure. The array of
cells I am talking about is cells covering a whole spreadsheet. The cells
will contain both numbers and text, it is the numbers I am interested in.

 

[Bob Davison]

Thanks Bob,
I have tried the formula and it doesn't return any results, is it possible
to make it add the numbers in the cells and return the figure. The array
of
cells I am talking about is cells covering a whole spreadsheet. The cells
will contain both numbers and text, it is the numbers I am interested in.

Biff's (T. Valko) idea is much better than mine. You can use his formula to
strip out all the numbers, one cell at a time and then add them up. I don't
know how to do it all at once with one formula.

The formula I came up with does work but I had a problem copying it from my
reply. In order to get it to work, it must be inserted as one continuous
line of code, not individual lines. I had to "reconnect" the code using one
backspace at each break. A little tricky but then it works fine.

Bob

 

[Guest]

Thanks to the both of you for helping.

Biff's (T. Valko) idea is much better than mine. You can use his formula to
strip out all the numbers, one cell at a time and then add them up. I don't
know how to do it all at once with one formula.

The formula I came up with does work but I had a problem copying it from my
reply. In order to get it to work, it must be inserted as one continuous
line of code, not individual lines. I had to "reconnect" the code using one
backspace at each break. A little tricky but then it works fine.

Bob