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?

"Boenerge" wrote in message
...

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&"012345678 9"))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(A 1,255)&"0123456789")),LEN(A1))&"abcdefghijklmnopqr stuvwxyz
"))-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

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&"01234567 89")),ROW(INDIRECT("1:255"))))

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

Biff

"Bob Davison" wrote in message
...

"Boenerge" wrote in message
...

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&"012345678 9"))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(A 1,255)&"0123456789")),LEN(A1))&"abcdefghijklmnopqr stuvwxyz
"))-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

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" wrote:


"Boenerge" wrote in message
...

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&"012345678 9"))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(A 1,255)&"0123456789")),LEN(A1))&"abcdefghijklmnopqr stuvwxyz
"))-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

"Boenerge" wrote in message
...
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

Thanks to the both of you for helping.

"Bob Davison" wrote:


"Boenerge" wrote in message
...
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