Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number =MONTH(1&A1) =–TEXT(1&A1,"m") In case, cell A1 contains the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).
Downloads 18 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – Sales
Disclaimer – The datasets are generated through random logic in VBA. These are not real sales data and should not be used for any other purpose other than testing. Other data sets – Human Resources Credit Card Bank Transactions HR Analytics Note – I have been approached for the permission to use data…
Downloads 17 – Sample CSV Files / Data Sets for Testing (5 Million Records) – Credit Card
Disclaimer – The datasets are generated through random logic in VBA. These are not real credit card data and should not be used for any other purpose other than testing. Other data sets – Human Resources Sales Bank Transactions HR Analytics Note – I have been approached for the permission to use data set…
Solution – Challenge 64 – Sum up the Range where a particular alphabet appears
Below is a possible solution to the Challenge 64 – Sum up the Range where a particular alphabet appears =SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))
Excel Quiz 38
Downloads 16 – Sample CSV Files / Data Sets for Testing – Human Resources (5 million records)
Disclaimer – The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing. Other data sets – Sales Credit Card Bank Transactions HR Analytics Note – I have been approached for the…
VBA – Data Masking or Anonymize the Data through a Macro
Sometimes, you may need to sanitize your data before sharing your file. The below macro would sanitize the data and since this is completely based on random generation, hence, it can not be recreated. Make sure that you have a copy of the Excel sheet before you sanitize or mask or anonymize the data by…
Article 47 – New Functions in Excel 2016
This is a guest post contributed by Hannah Sharron of http://spreadsheeto.com/ Microsoft has added six new built-in functions with the release of Excel 2016. These functions are also available in Office 365. In this article, we will take a quick look at three of those new functions. TEXTJOIN For a long time, the CONCATENATE has…
Challenge 64 – Sum up the Range where a particular alphabet appears
Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone. To check your answer, the sum will be 27 for above. The file related to this challenge can be downloaded from Challenge 64 – Sum up the Range where a…
Solution – Challenge 63 – Convert to Date Format
Below is a possible solution to the Challenge 63 – Convert to Date Format Put following formula and drag down =IFERROR(–SUBSTITUTE(A1,",",""),–SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,",","")," ","*",2),"*",", "))