calculating a sum from cells that contain letters and numbers
 

I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and text
in the final result. Please note that the symbol and the text never changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

if is always a "< " in front and a " U" in back
set up a helper column with
=value(left(right(entry,Len(entry)-2),len(entry)-4))
sum this column

"Gerry" wrote:

I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and text
in the final result. Please note that the symbol and the text never changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will delete all alpha characters from the selection.

Vaya con Dios,
Chuck, CABGx3



"Gerry" wrote:

I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and text
in the final result. Please note that the symbol and the text never changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

Thank you. This works well.

"bj" wrote:

if is always a "< " in front and a " U" in back
set up a helper column with
=value(left(right(entry,Len(entry)-2),len(entry)-4))
sum this column

"Gerry" wrote:

I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and text
in the final result. Please note that the symbol and the text never changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

If it is always < and U then you can use

=SUMPRODUCT(--(0&TRIM(SUBSTITUTE(SUBSTITUTE(A1:A10,"<",""),"U"," "))))

to sum A1:A10 for instance, then just use a custom format like "< "0.00" U"

in the cell with the formula



--
Regards,

Peo Sjoblom



"Gerry" wrote in message
...
I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and
text
in the final result. Please note that the symbol and the text never
changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

=SUM(--MID(A1:A100,3,LEN(A1:A100)-4))
or
=SUM(--SUBSTITUTE(SUBSTITUTE(A1:A100,"< ","")," U",""))

In each case, array entered (Control Shift Enter) .
--
David Biddulph

"Gerry" wrote in message
...
I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and
text
in the final result. Please note that the symbol and the text never
changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.

calculating a sum from cells that contain letters and numbers
 

Note that if there is a blank cell in that range both formulas will return
errors


--
Regards,

Peo Sjoblom





"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUM(--MID(A1:A100,3,LEN(A1:A100)-4))
or
=SUM(--SUBSTITUTE(SUBSTITUTE(A1:A100,"< ","")," U",""))

In each case, array entered (Control Shift Enter) .
--
David Biddulph

"Gerry" wrote in message
...
I continually evaluate data that comes in this form:

<"number" "text" ex. - < 0.3 U

I need a way to obtain the total while keeping the less than symbol and
text
in the final result. Please note that the symbol and the text never
changes
for a given data set. The data set tends to be upwards of 100 cells.

Any help will be greatly appreciated.

Thank you.