Get the initial (middle name) from a triple name in a cell.
C4 The cell contains the triple name separated with spaces.=LEFT(C4,1) & "." & MID(C4, SEARCH(" ", C4, 1) + 1, 1) & "." & MID(C4, SEARCH(" ", C4, SEARCH(" ", C4, 1) + 1) + 1, 1) & "."
Category: Formulas – combined functions
Two or more functions = One Formula, how to combine more than one function to present a powerful formula that does the job well
Jan8 date format
Format a date in shortest format possible indicating how many days have passed (Past date)
If date1-date2 < 8 Sat, Fri, …
If date1-date2 < 350 Dec24, Jan4, Aug14
Else 2020Feb4
Walking Columns
An easy way to scroll through set of data in a graphic way without graphs.
A-pure-formula xlsx workbook. These were done using formulas, conditional formatting, and ActiveX control.
Get 3rd Wednesday of month
This was an interesting request.
Needing to get the date of 3rd Wednesday for a given month, automatically, using pure Excel functions.
So, sharing here on what is the formula and how to get there.
Repeated Offset
I needed to have an offset of columns to be repeated every 12 months.
This is part of a project, we needed to calculate the coefficient of sales prediction based on the month number, we do have coefficient saved in another table that repeated every year (12 months).
Sorting with functions only
This one is similar to http://xlfxs.com/sort-list-using-functions/ where we use some controls here that make it more user-friendly.
6174 Kaprekar’s constant
This is the spreadsheet to prove this constant. When all roads lead to Kaprekar
Just enter any 4 digits number in D3 and watch roads walk you 6174
Cuts string of list of items
An old lesson to one of my early classes to show how can we cut a string having list of items into its parts. (At least 1st three parts) using formulas only.
Excel @ before function names in formulas
Looks like there is a change coming. Excel started adding the symbol @ to some formulas, not sure why only those, but we are waiting to start using this new feature.
Return 12 values in one number
A technic I loved using, return list of items (up to 12 items now) in ONE number.
Let us say you have list of 12 items, and you want a function that will return some of that list (with no order), then this is how you can do it. This has been sitting around for awhile in my archive. I called it 12Return4095
Searching table in 2 dimensions.
Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.
This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.