| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cut the last 20 characters out of a text string
=LEFT(A1,LEN(A1)-20)
-- Gary''s Student - gsnu200908 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I remove unwanted characters within a text string. | Excel Discussion (Misc queries) | |||
| Text String - Specific Characters | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| Insert characters in a text string | Excel Worksheet Functions | |||
Linear Mode
