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.
Author: Vijay A. Verma
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/
Excel Quiz 49
Downloads 21 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – Bank Transactions
Disclaimer – The datasets are generated through random logic in VBA. These are not real banking transaction data and should not be used for any other purpose other than testing. Other data sets – Human Resources Credit Card Sales HR Analytics Note – I have been approached for the permission to…