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…
Excel Quiz No. 48
Excel Quiz No. 47
Excel Quiz 46
VBA – Optimize File Size by Clearing Unused Area
Sometimes, you find that your file size is very huge even though data contained in not much. The reason for this is that there are unused formatting beyond your data range. Either you can clear off your rows and columns beyond data range but if this is frequent, you can use a macro to do…