Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Cells on letters and numbers and placing result in one of 3 columns | Excel Worksheet Functions | |||
Finding most common occurence of values in cells containing letters and numbers | Excel Worksheet Functions | |||
Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) | |||
How can I merge 2 cells with numbers and letters? | Excel Worksheet Functions |