How do you extract a word from a cell which contains a sentence and Can i use multiple mid functions in a formula?

Answer 1

You can use a number of functions, depending on what you are doing. The main ones would be:

Find

Left

Right

Mid

Len

Find will do a search for the text you are looking for and give you the number of the position of the character in the text. So say you had the word ANKLE in A1. If you put a formula like the following one in it will give you the value 3:

=find("K",A1)

You can use FIND to find the start point of the word you are looking to extract and then use MID to extract it. As MID requires a starting point in the text, FIND will get it for you.

So say you were looking for the word fast out of the sentence Motor cycles are very fast vehicles which was in cell A1, the formula would be:

=MID(A1,FIND("fast",A1),4)

You can use Left and Right to extract letters from the beginning of a sentence or from the end of it. So using the above sentence again:

=Left(A1,5) will give you Motor

=Right(A1,5) will give you icles

Len gives you the length of a piece of text, which can be useful too.

You could use Mid more than once, but you will find better ways of doing things sometimes. It depends on what exactly you are trying to do. You can also bring bits of text together using the & to do so.

=LEFT(A1,17) & RIGHT(A1,8) will give you Motor cycles are vehicles

Say you have the following in A7:

Today is the first day of the rest of your life

=MID(A7,14,5)&" "&MID(A7,31,4) will give you first rest

Note here that I have used a space between double quotes in the &" "& part to put between the two words for proper punctuation . That can be useful to do too, because sometimes you'll find you will extract several words and there won't be spaces between them. You can use it to add spaces or other punctuations like commas or colons, whatever you need, especially if they are not in the original text. You can also put words inside those quotes to construct new sentences.

There is a lot there that I have shown you and that you can start experimenting with to try to do what you want. With practice and experience, you'll find lots of uses for these functions and some others. Set yourself a few simple tasks and see if you can do them. You will learn that way. You obviously know a bit already about the Mid function. With imagination, there is lots of things you can do.