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: Texts and Strings
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
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.
Cells with additional spaces
Detect which cells has additional spaces added by mistake by user.
=IF(AND(Left(F2, 2) = "No", LEN(F2) >2, "Space at end", "")
Always get 1st name (or last)
Formula to get first name from 2 formats either (Lastname, First) or (Firstname Last) formats.
VALUE() of LEFT()
I have been in a lot of situation needing to convert a number came out from a string into actual numbers. Sometimes N() worked, sometimes not, others VALUE() worked, but I did not see a function-does-all solution.
DayName
Simplest way to get name of day as cell output from a date.
Because you can custom format a cell with date to show only that day name
This is not that.
Fixed length ID
I needed few months back to make a fixed-length ID iut if none-fixed-length numbers.
SO I used the function REPT with RIGHT to force all numbers to be converted into fixed-length ID (mainly having leading zeros)
And here is the formula
Worksheet name, dynamically using formula
Get the name of the active worksheet in active workbook, using formulas only
This function needs to have workbook saved
Then, you just paste below formula into any cell …
Text-to-Columns, dynamically using formulas
I often use the technique of concatenating columns into 1 cell with separators.
Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.
And then, because of that, I need to extract that back, into table