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: MID
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.
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
Extract current (Active) workbook name
I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is…. And if we want to go 1 step further, I often have the version of the tool as part of the filename,…
Extract Address into table
Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table
Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above
Which is usually what you got from any list of addresses online, and you want to convert it to table.
This is exactly what one of clients had and needed, so here is the file that does that.
Basically you need to have 5 columns with formula in column G as:
=OFFSET($B$1,(ROW()-5)*4+4,0)
to extract first line (Starting from cell B5
Then in column H as:
CamelCase to Camel Case
Here is a good practice for combining functions to get a full smartsheet It basically add a space before any capital letter in a text, helpful when you have strings without spaces and want to convert them. It is all in formulas. Put additional spaces before any upper case letter. made specially for custom request….
MID + SEARCH to convert cell to rows
I have been there and done that more than once before This time I had a really large cell, more than 3k characters need to be converted into rows, and here is how you can do it. This one uses MID with SEARCH to do that Use one column to get the character number of…
Hyperlink usage and HyperlinkOf UDF
The Hyperlink function is really powerful and yet not widely used. You can create a whole navigation system with Hyperlink fx or create a custom jump to link to take user to certain area. Of course the “Jump-to” link need other functions, like Match, Indirect and Index Here is a sample of what we can do…