Formula to delete rightmost N characters
 

I have some cell containing text of various lengths. I'd like to delete the
rightmost 5 characters of each cell.

What comes to mind is LEFT(A1, LEN(A1) - 5). Is there a simpler way?

I'm using Excel 2005.

Thanks.

Formula to delete rightmost N characters
 

Your formula looks almost perfect to me. Only thing I'd change is make it:

=LEFT(A1,MAX(0,LEN(A1)-5))

In case any of your text is less than 5 characters in length.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Eric_NY" wrote:

I have some cell containing text of various lengths. I'd like to delete the
rightmost 5 characters of each cell.

What comes to mind is LEFT(A1, LEN(A1) - 5). Is there a simpler way?

I'm using Excel 2005.

Thanks.

Formula to delete rightmost N characters
 

Thanks.

I sometimes wish Excel had a more complete set of functions. It's typically
possible to manipulate existing functions to do what's needed, but it's
sometimes more difficult or convoluted than necessary. Whenever I see a
formula where the same value appears twice (like A1 in LEFT(A1, LEN(A1) - 5)
), it seems somehow un-aesthetic or at least un-economical. It's not so bad
if the duplicated text is short, like A1, but I often find myself repeating
lengthy sub-formulas because the function I really want is missing.

For example, what's needed here is a function such as LOPOFFRIGHT(A1,5),
which lops off the rightmost 5 characters. Obviously a similar LOPOFFLEFT
function.

Someday I'll teach myself VBA so I can write these formulas myself.

Anyway, thanks for your help.

"Luke M" wrote:

Your formula looks almost perfect to me. Only thing I'd change is make it:

=LEFT(A1,MAX(0,LEN(A1)-5))

In case any of your text is less than 5 characters in length.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Eric_NY" wrote:

I have some cell containing text of various lengths. I'd like to delete the
rightmost 5 characters of each cell.

What comes to mind is LEFT(A1, LEN(A1) - 5). Is there a simpler way?

I'm using Excel 2005.

Thanks.