taking off 5 characters from right side of cell data

  • Thread starter Thread starter Carla
  • Start date Start date
C

Carla

I know the right and left function, but it doesn't work
for what I am trying to do. I have a list of items in a
column and they all end in the word "total". I want to
take that word off of each line. The leading characters
are not a set amount, they vary, so I am unable to say
=left(a2,10).

Thank you!
 
Try this:

=LEFT(A2,LEN(A2)-6)

First, the formula evaluates the LEN function which determines the length of
the string. We subtract 6 from the result (6 is the length of the word
"Total" plus a leading space). The result of this is the length of the
string we want to extract. We can then use the LEFT function to return only
what we want.

HTH


--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh
 
Hi

I usually use Find Replace for this. In the Find box typ Total (with a space
before if necessary) and leave the Replace with box blank
 
Use the Mid function (in either VBA or Excel UI). It does what Left and
Right can't:
Mid(string, starting character, # of characters)
Mid(A2, 1, Len(A2)-5)
Translation: starting with the 1st character of A2, return the rest of A2
except for the last 5 characters.

HTH,
 
Back
Top