To extract last 2 words, use below formula =IF(ISNUMBER(FIND(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") To make a generic formula, to extract last 2 words =IF(COUNTIF(A2,"* *")>0,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") Now, you would need to change * * and 2 only which are marked in Red. If you need to replace last 3 words, then * * would be…
Category: Tips and Tricks
Inserting a Sequence 1,1,1,2,2,2,3,3,3,4,4,4….- Power Query – Tips & Tricks 2
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,1,1,1,2,2,2,2,3,3,3,3 and so on.
Tips & Tricks 177 – Extract first 2 words OR first n words
You can use following formula to extract first 2 words from a text string =IFERROR(LEFT(A2, FIND(" ", A2&" ", FIND(" ", A2) + 1) – 1),"") A generic formula to extract first 2 words which can be extended to n words =IFERROR(LEFT(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(A2&" "," ",REPT(" ",LEN(A2)),2))-1),"") =IFERROR(REPLACE(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(A2&" "," ",REPT(" ",LEN(A2)),2)),LEN(A2),""),"") To extract first n…
Count Number of Words – Power Query – Tips & Tricks 1
Many a time, you are required to count number of Words in a record. The classic formula in Excel happens to be =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1 Note – If you have blank cells also, then right formula is =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+(LEN(A2)<>0) To calculate, you can always mimic the above in a custom column in PQ by following =…
Tips & Tricks 176 – Create a Desktop Shortcut for Onedrive Documents
Sometimes, you might need to create a desktop shortcut to Onedrive documents. Below is a method for Excel documents which can be used for any MS Office document. First, you would need to find the path of Excel.exe on your computer.
Tips & Tricks 175 – Color Code for Official Color of Excel
Everybody sees the dark green color of Excel. The color code for this color is
Tips & Tricks 174 – VBA – OR Condition in FIND
FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be…
Tips & Tricks 173 – Data Validation for Case Sensitivity
There are scenarios when you want to put a data validation which is case sensitive. Hence you want USA to be entered as USA not as usa or uSA or uSA so on. You just want all capitals. Similarly, sometimes you need only small letters say maverick. Hence, not Maverick, mavericK etc.
Tips & Tricks 172 – Calculate Next Working day if date is of Weekend / Holiday
Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date. For example – 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or…
Tips & Tricks 171 – Convert your formulas to Absolute References
If your sheet has plenty of formula and you want to convert them into Absolute references i.e. One way is that you convert each one of them individually or use some logic for Find and Replace. But all these methods have their own limitations.