Cut the last 20 characters out of a text string
 

Hi, I have a column with text in each cell but the length of the text string
varies in each cell. However I would like to remove the last 20 characters of
the text string.

I could do this if I wanted to only show the last 20 characters by using
right(a1,20) but I cannot use the left function because the length of the
text string varies depending on the name of the person.

EG The first 2 cells are

Lott M 123456789101234567890
Peterson D 123456789101234567890

And I would like them to say

Lott M
Peterson D

I'm sure there will be a simple way of doing this but can't find one.

Thanks in advance.

Cut the last 20 characters out of a text string
 

=LEFT(A1,LEN(A1)-20)
--
Gary''s Student - gsnu200908

Cut the last 20 characters out of a text string
 

=TRIM(LEFT(A8,LEN(A8)-20))

in your example there is an extra 1 which will result in
Lott M 1
Peterson D 1


If this post helps click Yes
---------------
Jacob Skaria


"Michael" wrote:

Hi, I have a column with text in each cell but the length of the text string
varies in each cell. However I would like to remove the last 20 characters of
the text string.

I could do this if I wanted to only show the last 20 characters by using
right(a1,20) but I cannot use the left function because the length of the
text string varies depending on the name of the person.

EG The first 2 cells are

Lott M 123456789101234567890
Peterson D 123456789101234567890

And I would like them to say

Lott M
Peterson D

I'm sure there will be a simple way of doing this but can't find one.

Thanks in advance.

Cut the last 20 characters out of a text string
 

Simples... thanks.

"Gary''s Student" wrote:

=LEFT(A1,LEN(A1)-20)
--
Gary''s Student - gsnu200908

Cut the last 20 characters out of a text string
 

If you want to pull out the characters, which is available before the numeric
characters, then use the below formula.

=LEFT(TRIM(A1),FIND({1,2,3,4,5,6,7,8,0},TRIM(A1))-2)

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Michael" wrote:

Hi, I have a column with text in each cell but the length of the text string
varies in each cell. However I would like to remove the last 20 characters of
the text string.

I could do this if I wanted to only show the last 20 characters by using
right(a1,20) but I cannot use the left function because the length of the
text string varies depending on the name of the person.

EG The first 2 cells are

Lott M 123456789101234567890
Peterson D 123456789101234567890

And I would like them to say

Lott M
Peterson D

I'm sure there will be a simple way of doing this but can't find one.

Thanks in advance.