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.)
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.
This is the formula your lecturer seems to require.
=MID($C2,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-3)),LEN($C2))-IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)-1)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.
=TRIM(MID($C2,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1,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 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])
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.