Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Extracting words.


Hi, i am having difficulty to extract particular words in a sentence in one cell to another. please take a look at the Excel file i have attached. I already pulled in the first word of a sentence to another cell but i'm confused to pull the 2nd, 3rd and 4th sentence. I would like to know the formula to complete this excel question given by my lecturer. 

thank you in advance. I'm waiting to your answer.



Selected Answer

This is the formula your lecturer seems to require.


In my opinion it isn't the one to be used, however, because it returns strings starting with a blank space. The desired result still includes the leading blanks. However, I would eliminate them by embedding the above in a TRIM() function. The resulting formula would be the following.


The formula is designed to be identical for all cells in all columns. It is also designed to be copied into cell D2 and copied from there to other cells in your table.

Note that column D is the fourth column. The function COLUMN() returns the number of the column in which it is found. When placed in column D it returns 4. COLUMN()-3 returns a different number, depending upon which column the function is in. When in column D, Column()-3 returns 1. In column E it returns 2 etc. COLUMN()-4, when found in column D returns 0, in column C it would be -1.

Now, the MID() function has this syntax:-
=(MID([Text], [Start], [Number of characters])

  1. In the above formula [Text] = $C2
    The column must be absolute because the formula will be copied to the right and the Text never changes.
  2. [Start] is the number of the first character to be included in the mid-string:
    = IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1
  3. [Number of characters] = IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-3)),LEN($C2))-IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)-1

The SUBSTITUTE() function has the following syntax.
= SUBSTITUTE([Text], [Old Text], [New Text], [Instance])
You can substitute the nth instance of a comma with another character. The formula uses the counter COLUMN()-3 to determine the instance (1st comma in column D, second comma in column E etc). The formula replaces the comma with CHAR(160), which isn't normally found in text, and then lets the FIND() function find its position in the string.

The SUBSTITUTE() function will return an error if the [Instance] is zero or it is greater than the number of available instances in the original text. Therefore each FIND(SUBSTITUTE()) function is embedded in an IFERROR() function.

The IFERROR() function has the following syntax= IFERROR([Function], [Alt Function or Result]). In the above formula, if the SUBSTITUTE() function fails (because the instance wasn't found) the FIND() function will also fail, resulting in an error which triggers the alternative. The alternative result or function is different for the various uses.

Your lecturer was well aware that you might try to get a solution from the Internet. Therefore he/she asked for explanations. I have provided the function and explained how it works. Don't think for a moment that you will be home scot-free with a simple copy/paste. Your part of the deal is to study what I have explained and fully understand what the function does and how it does it. What I am giving you is just a pointer. You will still have to invest an hour for study. If you need clarification please don't hesitate to ask.



sorry but both of your formula didnt work, I dont understand?? 
I had replace the "," to ";" as I am using Excel in 2013 version but still the result did not the same as the table example no 3. The result after i typed your formula was that it copied the whole sentence instead of the certain part. Also can you please explain what is the function of "char" and its function in this formula?
Fida_mutia (rep: 22) Jun 3, '19 at 1:50 pm
Both formulas were tested. They do work on your workbook. Some language versions of Excel use the semicolon as separators instead of comma. That is unrelated to the year of issue. In the workbook you posted the separator used in column D is a comma. I attach a workbook to my answer now where the formula has been endered in D2 and copied form there to D2:H4.
The CHAR() function returns a string of a single character specified by the numbe in its parameter according to ASCII. For example =CHAR(65) will produce an "A". CHAR(160) is a character not usually contained in gtext because it isn't included in most character sets. =CHAR(160) will probably produce a question mark on your PC but that is because Excel can't interpret it properly (not because it's a question mark).
Variatus (rep: 4879) Jun 4, '19 at 1:07 am
Thank you for awarding points to my answer after all this time. I take this to mean that you found my explanations helpful. That makes me feel rewarded because that was my intention. Have a great day Fida_mutia!
Variatus (rep: 4879) Jan 10, '20 at 9:18 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login