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…
Downloads 22 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – IBM HR Analytics for Attrition
On Kaggle there is a data set published named "IBM HR Analytics Employee Attrition & Performance" to predict attrition of your valuable employees. This is a very popular dataset and has usability index of 8.8. https://www.kaggle.com/pavansubhasht/ibm-hr-analytics-attrition-dataset But this data set has only 1470 rows whereas we need, sometimes, a large data set for testing. So,…
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…
Excel Quiz 51
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 =…
Excel Quiz 50
Article 50 – SEQUENCE Function
SEQUENCE is one of the functions introduced in 2020 for Office 365 subscribers as part of Dynamic Array functions. I have published this article @ Linked. Click below SEQUENCE FUNCTION
Article 49 – SWITCH Function
SWITCH is one of the underused functions in Excel and this article is an attempt to encourage folks to use SWITCH function which is like SWITCH function used in Java and CASE – SELECT of VBA. This article I have published on Linkedin. Below is the link for that. https://www.linkedin.com/pulse/excel-switch-function-excel-vba-power-bi/