Text Handling Functions in Excel
How useful would it be to be able to put two names together or remove a part of a sentence, or an part number and keep only one part of it,.... this can be done with the text handling functions. Some basics have been covered in the basic text section, look at part 2 for more functions.
Extracting text out of a bigger text
The functions for this are LEFT, MID, RIGHT and are used as follow.
If you know where the word you want is you can just use the RIGHT, LEFT and MID (for Middle) function.
I love eating..... the word love starts at the 3 letter of the sentence. LOVE has 4 letters so if you want to extract it
use:
MID("I love eating", 3,4) and it will give you the word starting at the 3rd letter and that is 4 letter long. Piece of cake isn't it?
LEFT("I love eating", 5) will give you "I lov". 5 letters from the left.
RIGHT("I love eating", 6) will give you "eating". 6 letters from the right.
LEN("I love eating") or LEN(A19) will give you the lenght of the sentence or the cell. Here it will be 13.
If You DO NOT KNOW where the word "LOVE" is, then you can use the function SEARCH("love", "I love eating") or SEARCH("love", A22) which refers to the cell A22.
This will give you the coordinates of the word "LOVE" and so you can then use the MID function very easily..... like MID(A22,SEARCH("love", A22),4)
Then how to replace a word with another?
Use the Replace function in conjunction with the SEARCH function.
If you want to replace "I LOVE EATING" with "I HATE EATING"
do:
REPLACE("I love eating", 3,4,"hate")
or REPLACE(A26, SEARCH("love",A26),4,"hate") and in the cell A26 is the sentence "I love eating"
But here you still need to know the lenght of the words you want to replace so you can use the function LEN("WORD") which give you the lenght of a word.
REPLACE(A26, SEARCH("love",A26),LEN("love"),"hate")
where A26 is the sentence "I love eating"
BUT the is a much simpler solution if you just want to replace a word.......it is to use the function SUBSTITUTE!!
You can see from the last sentence that "lov" will not be replaced because it is different from "love"
You can download these examples here.
AMPERSAND function (&)
With this simple greek letter you can glue or concatenate words together too.
="I love" & " Eating" makes "I love Eating.
= CONCATENATE("I love", " ", "Eating")
=CONCATENATE("I love ", A2), where A2 is the name of your loved one.
With these functions just described you can handle 99% of the problems you could encounter in EXCEL. There are a few more function listed here under.
So that's it for the Text Handling Functions in Excel.
Now, let's use them....