There is a column in excel want to extract the word of the from the beginning of values. Just the word of the must extract.
CodePudding user response：
In cell B1 ... =IF(C1=A1,"",LEFT(A1,3))
In cell C1 ... =TRIM(IF(LEFT(UPPER(A1),4) = "THE ",MID(A1,4,1000),A1))
Just move your references as need be.
CodePudding user response：
How about:
IFERROR(MID(A2,FIND(" ",A2,1) 1,LEN(A2)),A2)
IFERROR(LEFT(A2,FIND(" ",A2,1)-1),"")
CodePudding user response：
Here is a solution that you can use :
Cell F1 has the key word you would like to extract out I have given 3 example ( "the" , "is" , "very")
The logic is
- Column F I determine if the strings starts with the key word . I use MID and LEN functions. It gives a logical True or false
- In Column G I show the word to be extracted if the string matches (i.e. Column F is True else a nothing)
- In Column H I show the rest of the string if the word need to be extracted
This is dynamic . as you change the value in Cell F1 it will show you the extracted data accordingly.
Example 1 : with keyword "the" Picture-04
Example 2 : with keyword "is" Picture-05
Example 3 : with keyword "very" Picture-06
Note the Column F is not necessary - you can as well embed this formula into the Column G and Column H formula. I just split it for easier understanding.