Deleting part of the infomation in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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