Microsoft Excel LEN, SEARCH & TRIM
Functions are mostly used to find out the length of text, location of the text
string inside another text & for removing the extra spaces within the text.
The Microsoft Excel LEN function
returns the length of a given text as by the number of characters & also count
the character in numbers.
The Microsoft Excel SEARCH
function returns the location of a specific text portion inside another text.
This function shows us the position of text & also allows wildcards (*,?).
The Microsoft Excel TRIM
function is used to remove the extra spaces, leaving only one space between
words & no space in the start or at the end of the text.
Purpose:
To find out the length of text,
location of text string inside another text & for removing the extra spaces
within the text.
Syntaxes of LEN, SEARCH & TRIM Functions:
The syntaxes of LEN, SEARCH &
TRIM Functions in Excel are as follows;
- =LEN(text)
- =SEARCH(find_text, within_text, [start_num])
- =TRIM(text)
Return value:
Number of Characters, location of
text & removing extra spaces.
Arguments:
- Text - The text for which we want to calculate length & remove extra spaces.
- Find_Text – Specific portion of text to find out.
- Within_Text – The text to search within.
- Start_Num – (optional) Starting position in the text to search, while the default is 1.
You can download free
the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below
download icon,
This article explains in simple terms how to
use LEN, SEARCH & TRIM Functions. It takes a step-by-step approach, &
following the below contents, we will touch in this post.
- Microsoft Excel LEN Function explanation through example.
- Microsoft Excel SEARCH Function explanation through example.
- Microsoft Excel TRIM Function explanation through various examples.
Let’s start to learn something
new about the LEN, SEARCH & TRIM Functions of Microsoft Excel.
1. Microsoft
Excel LEN Function.
Microsoft Excel LEN Function returns
the length of a given text as by a number of characters & also count the
character in numbers, So now we explain LEN Function through example, as shown
in below example,
Explanation: - In this
example we have taken the information of a company chart of accounts, from
"Row 2" to "Row 11" which contains the
information of Budget Code, Description & the last "D"
column contains a formula that how we can use the LEN Function to get
our specific return. As we see that the cell "D8" contains the
LEN Function & "B8 & C8” are our given information, so we
want to check or find out the number of characters in cell "C8",
when we use the LEN Function, it returns in cell "D8"
the answer is 8, while we see that the text "stove"
have five characters but the result is 8, actually 3 other
spaces available in cell "C8" instead of characters.
In this scenario, we have used a
formula like below,
=LEN(C8)
|
2. Microsoft Excel SEARCH Function.
Microsoft Excel SEARCH Function returns the location
of a specific text portion inside another text. This function shows us the
position of text & also allow wildcards (*,?), so now we explain SEARCH
Function through example, see the below example,
Explanation: - In this
example we have taken the information of a company chart of accounts, from
"Row 2" to "Row 11" which contains the
information of Find Characters, Description, Start from & the
last "E" column contains a formula that how we can use the SEARCH
Function to get our specific return. As we see that the cell "E7"
contains the SEARCH Function & "B7, C7 & D7” are our given
information, so we want to check or find out the "V?"
characters inside the cell "C7", when we use the SEARCH
Function, it returns in cell "E7" the answer is 8,
while we see that in the text "Microwave Oven" the first "V?"
characters are available after 8 characters. The symbol of "?"
is wildcard which represents a variable character.
In this scenario, we have used a
formula like below,
=SEARCH(B7,C7,D7)
|
3. Microsoft Excel TRIM Function - Example 1.
Microsoft Excel TRIM Function
is used to remove the extra spaces, leaving only one space between words &
no space in the start or at the end of the text, so now we explain TRIM Function
through various examples, see the below examples,
Explanation: - In this
example we have taken the information of a company chart of accounts, from
"Row 2" to "Row 11" which contains the
information of Budget Code, Description & the last "D"
column contains formula that how we can use the TRIM Function to get
our specific return. As we see that the cell "D6" contains the
TRIM Function & "B6 & C6" are our given information, so
we want to remove extra spaces from cell "C6" when we use the
TRIM Function, it returns in cell "D6" the answer is "Television",
while we see that the text " Television"
in cell "C6" have two space in the start of a word, so TRIM
Function removed the extra space from the start of a word.
In this scenario, we have used a
formula like below,
=TRIM(C6)
|
Example 2.
If in the case when the text has so many
spaces between the words, so then see the magic of TRIM Function in the below
example,
Explanation: - In this
example we see that from "Row 2" to "Row 11" which
contains the information of Budget Code, Description & the
last "D" column contains a formula that how we can use the TRIM
Function to get our specific return. As we see that the cell "D9"
contains the TRIM Function & "B9 & C9" are our given
information, so we want to remove extra spaces from cell "C9",
when we use the TRIM Function, it returns in cell "D9"
the answer is "Water Cooler", while we see that the text in
the cell "C6" is like this " Water Cooler" which have so
many spaces between the words, so TRIM Function removed the extra space from
between two words.
In this scenario, we have used a
formula like below,
=TRIM(C9)
|
Example 3.
If in the case when the text
have line breaks & spaces at the same time, so then see how we use the
TRIM Function in the below example,
Explanation: - In this
example we see that from "Row 2" to "Row 11" which
contains the information of Budget Code, Description & the
last "D" column contains formula that how we can use the TRIM
Function to get our specific return. As we see that the cell "D5"
contains the TRIM Function & "B5 & C5" are our given
information, so we want to remove line breaks & extra spaces at same time
from cell "C5", when we use the TRIM Function, it
return in cell "D5" the answer is " Water &
Cooler", while we see that the text have a line breaks in cell "C6"
& having also extra spaces, so TRIM Function removed the line breaks & the
extra space from cell "C6".
In this scenario, we have used a
formula like below,
=TRIM(C5)
|
No comments:
Post a Comment