You can use the LEFT and RIGHT functions in
the Microsoft Excel
spreadsheet program along with the LEN (length) function to remove the leftmost
or rightmost character from a text
string. These functions also work in
Google Sheets,
LibreOffice Calc,
which is the spreadsheet component of the
LibreOffice software
package, and
Apache OpenOffice Calc, which is the spreadsheet program included in
Apache OpenOffice,
though in the Apache OpenOffice Calc program you need to substitute semicolons
(;) for commas (,) in the formulas. E.g.,
in Apache OpenOffice Calc you would need to use =RIGHT(A5;LEN(A5) -1)
, instead of =RIGHT(A5,LEN(A5) -1) as you would in the other
programs.
The syntax for the RIGHT function is RIGHT(text,[numchars]).
If you don't include numchars, i.e., you use RIGHT(text)
then the value returned is the rightmost character in the string. E.g.,
if cell A1 has 1ABC in it, then =RIGHT(A1) returns
C. But suppose, instead, you want to remove the leftmost
charaacter from a string. You can use the RIGHT function to do
so. E.g., suppose I have a column of values, e.g.:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEF | |
| 3 | 3GHI | |
| 4 | 4JKL | |
| 5 | 5MNO |
If I want to remove the number at the beginning of each text
string and put the shortened strings in column B, I could, since the
strings are all 4 characters long, use =RIGHT(A1,3) in column B1
and then copy the formula down through the other cells in column B by clicking
in cell B1 and holding down the leftmost mouse button and dragging downwards
through the other cells in column B and then hitting Ctrl-D.
But suppose the strings vary in length. I.e., suppose I have a worksheet containing the following strings in column A:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEFG | |
| 3 | 3HI | |
| 4 | 4JKL | |
| 5 | 5MNOP |
In the above example, I could use the length (LEN) and right functions.
E.g., I could use the formula =RIGHT(A1,LEN(A1) - 1) in cell B1,
which would return ABC, but if I copied that formula into the
other cells in column B, it would chop off the leftmost character in the
cells in column A even when the length of the text varied, since the
LEN function would return the length of the entire string and
LEN(A1 - 1) would substract one from the length. The
RIGHT function would then count that many characters from the
right side of the string towards the left side, but, since the RIGHT function
is counting using length minus 1, it omits the very first character in the
string.
| A | B | |
| 1 | 1ABC | ABC |
| 2 | 2DEFG | DEFG |
| 3 | 3HI | HI |
| 4 | 4JKL | JKL |
| 5 | 5MNOP | MNOP |
The syntax for the LEFT function is LEFT(text,[numchars]).
If you don't include numchars, i.e., you use LEFT(text)
then the value returned is the leftmost character in the string. E.g.,
if cell A1 has 1ABC in it, then =LEFT(A1) returns
1. But suppose, instead, you want to remove the rightmost
charaacter from a string. You can use the LEFT function to do
so. E.g., suppose I have a column of values, e.g.:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEF | |
| 3 | 3GHI | |
| 4 | 4JKL | |
| 5 | 5MNO |
If I want to remove the last character from each cell in column A
and put the shortened strings in column B, I could, since the
strings are all 4 characters long, use =LEFT(A1,3) in column B1
and then copy the formula down through the other cells in column B by clicking
in cell B1 and holding down the leftmost mouse button and dragging downwards
through the other cells in column B and then hitting Ctrl-D to
copy the formula to the other cells.
But suppose the strings vary in length. E.g.:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEFG | |
| 3 | 3HI | |
| 4 | 4JKL | |
| 5 | 5MNOP |
I could use the formula =LEFT(A1,LEN(A1) - 1) column B1
to get 1AB in column B1. If I copied the formula down through
the other cells in B, I would get the following results.
| A | B | |
| 1 | 1ABC | 1AB |
| 2 | 2DEFG | 2DEF |
| 3 | 3HI | 3H |
| 4 | 4JKL | 4JK |
| 5 | 5MNOP | 5MNO |
The formula LEFT(text,LEN(text) - 1) functions similar to
the CHOP function in
Perl.
Tested with Excel 2016, LibreOffice 5.2.3.3, and Apache OpenOffice 4.1.3.
