Deleting part of the infomation in a cell

O

Office Lapis

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.
 
G

Gord Dibben

Try edit>replace

What: L

With: nothing

Replace all.


Gord Dibben MS Excel MVP

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

Mike H

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
 
S

Shannon W.

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.
 
Top