How to Trim or Remove Text in Excel

How to Trim or Remove Text in Excel

26.Jul.2021

will show you a couple of
ways to trim text or cut off text
characters within a string of text in
excel here I have some data which shows
a three digit code at the front let's
say that represents a department number
followed by a four digit code at the end
which represents a cost center number
now because all of the three digit
department numbers in this example are
the same let's try and trim this text so
that we just display the last four digit
costs enter numbers to do that I will
use the write function the write
function allows you to specify how many
characters from the right of the text
string that you want to see in this
example we want to see the last four
digits from the right to do that I'm
going to type equals sign write open
parenthesis and if you are using Excel
2007 or greater you can see here that it
is prompting you as to the order things
should be entered in the formula so it's
now asking which text to evaluate and
our text is here in cell a2 so I'm going
to click on cell a2 then type a comma
then I need to specify the number of
characters that I want Excel to display
which in this case is 4 followed by a
closed parenthesis so the final formula
is equal sign right open parenthesis a 2
comma 4 close parenthesis so it's saying
give me the last 4 characters from the
text in cell a2 if I now hit enter to
complete it you will see that only the
last 4 digits are displayed and if I
copy that formula down you will see that
it is displaying the last four digits
from each of these cells as well the
left function does the same task but it
returns text X amount of characters from
the left of the text string so if I just
wanted to display the first three
numbers which I said was the department
code I would enter equals
left open parentheses a two comma three
closed parentheses and then hit enter
and you can now see that the first three
digits are being displayed so that's
quite easy and straightforward what
happens though when you have a string of
text where the overall length is
different in each row for example the
five rows below represent department
names followed by a cost Center number
written in brackets at the end of the
text let's say I want to clean that up
for a report and I want to remove the
cost Center at the end I can't just use
the left function because when I specify
the number of characters to extract from
the left the number of characters
changes each time depending on the
department name so in the first one
insights Department my formula would be
equals left open parenthesis a 7 comma
19 closed parenthesis because the phrase
insights Department is 19 characters
long however when I get to the next one
the number of characters changes to 16
because there are 16 characters in the
words legal department so I can't create
a formula using the left function alone
and copy it down because it won't work
it won't give the text that I am looking
for what I need to do is combine the
left function with another function
called the Len function le n the Len
function actually counts the number of
characters in text so here for example I
have the word house which has 5 letters
if I use the Len function here it should
give me the number 5 so let's test it
equals Len open parentheses a 14 closed
parenthesis and hit enter and it's
returning the result of 5 to say there
are 5 characters in cell a 14 or 5
characters in the word house
so the Len function counts the number of
characters in the cell now if we look
back at our department names here we
know that the cost centers at the end
that we want removed are six characters
long and they have parentheses around
them so that makes them eight characters
long and it is those eight characters
that we want removed in order to count
or to calculate the number of characters
to the left of those cost Center numbers
we can use the Len function and then
subtract eight from it so let's just do
it here to see what it gives us equals
then open parentheses cell a7 closed
parentheses minus eight so count the
number of characters in cell a7 and
subtract eight from that number and the
answer becomes twenty if I copy that
formula down you'll see that the number
changes each time depending on the
department name so if I combine that Len
function with the left function I can
tell Excel to give me the leftmost
twenty characters in this cell or the
leftmost seventeen characters from this
cell or the leftmost twenty-one
characters from this cell and so on and
so forth so the final formula then which
I will put here in cell c7 will be
equals left open parenthesis a7 comma
Len open parenthesis a7 closed
parentheses minus eight closed
parentheses so I am saying here count
the number of characters in cell a7 and
subtract eight from that number and
whatever that result is which in this
case is 20 then give me the leftmost 20
characters from cell a7 if I then hit
enter and copy the formula down you will
now see that only the department names
are being displayed and the cost Center
numbers at the end have been trimmed
from the text
I hope that makes sense and I hope you
found it useful

We are social