I have been asked to give a total of all the Liters since 2006. In the excel
column there is a quantity using numbers but after the number is (L) for
liters. I wanted the total sum of about 2000 rows and cannot get it unless I
manually delete all the (L)'s from each cell individually. Is there a way
to take out all the (L)'s at the same time or am i going to have to do all
the 2000 entries separately.

Try editreplace

What: L

With: nothing

Replace all.


Gord Dibben MS Excel MVP

On Thu, 2 Aug 2007 11:20:08 -0700, Office Lapis <Office
wrote:

I have been asked to give a total of all the Liters since 2006. In the excel
column there is a quantity using numbers but after the number is (L) for
liters. I wanted the total sum of about 2000 rows and cannot get it unless I
manually delete all the (L)'s from each cell individually. Is there a way
to take out all the (L)'s at the same time or am i going to have to do all
the 2000 entries separately.

Hi,

Put this in a helper column alongside your data and drag down. It will
extract the numbers for you to add up.

=--LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Mike

"Office Lapis" wrote:

I have been asked to give a total of all the Liters since 2006. In the excel
column there is a quantity using numbers but after the number is (L) for
liters. I wanted the total sum of about 2000 rows and cannot get it unless I
manually delete all the (L)'s from each cell individually. Is there a way
to take out all the (L)'s at the same time or am i going to have to do all
the 2000 entries separately.

Go into your edit pull down and select replace. In the "Find what" field
type L and in the "Replace with" field, leave it blank. Then click the
"Replace All" button and it should take care of the entire column.

Hope this helps.

Shannon W.

"Office Lapis" wrote:

I have been asked to give a total of all the Liters since 2006. In the excel
column there is a quantity using numbers but after the number is (L) for
liters. I wanted the total sum of about 2000 rows and cannot get it unless I
manually delete all the (L)'s from each cell individually. Is there a way
to take out all the (L)'s at the same time or am i going to have to do all
the 2000 entries separately.

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

Vaya con Dios,
Chuck, CABGx3




"Office Lapis" wrote:

I have been asked to give a total of all the Liters since 2006. In the excel
column there is a quantity using numbers but after the number is (L) for
liters. I wanted the total sum of about 2000 rows and cannot get it unless I
manually delete all the (L)'s from each cell individually. Is there a way
to take out all the (L)'s at the same time or am i going to have to do all
the 2000 entries separately.