taking off 5 characters from right side of cell data

[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!

 

[Michael Malinsky]

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

 

[Andy B]

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

 

[George Nicholson]

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,

 

[Carla]

Thank you! You are awesome, worked like a charm!!!