Help!

Cut the last 20 characters out of a text string

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  cannot see the content of a file  
Author Message
Michael
External


Since: Jun 27, 2006
Posts: 394



PostPosted: Wed Nov 04, 2009 7:25 am    Post subject: Cut the last 20 characters out of a text string
Archived from groups: microsoft>public>excel>misc (more info?)

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.
Back to top
Gary''s Student
External


Since: Oct 01, 2005
Posts: 3403



PostPosted: Wed Nov 04, 2009 7:35 am    Post subject: RE: Cut the last 20 characters out of a text string [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=LEFT(A1,LEN(A1)-20)
--
Gary''s Student - gsnu200908
Back to top
Jacob Skaria
External


Since: Mar 04, 2009
Posts: 568



PostPosted: Wed Nov 04, 2009 7:37 am    Post subject: RE: Cut the last 20 characters out of a text string [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

=TRIM(LEFT(A8,LEN(ACool-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.
>
Back to top
Michael
External


Since: Jun 27, 2006
Posts: 394



PostPosted: Wed Nov 04, 2009 8:06 am    Post subject: RE: Cut the last 20 characters out of a text string [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Simples... thanks.

"Gary''s Student" wrote:

> =LEFT(A1,LEN(A1)-20)
> --
> Gary''s Student - gsnu200908
Back to top
Ms-Exl-Learner
External


Since: Sep 15, 2009
Posts: 48



PostPosted: Wed Nov 04, 2009 8:13 am    Post subject: RE: Cut the last 20 characters out of a text string [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum