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: LEFT
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.
Get column name (columnname as A,B,C, etc) as input inside cell
Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters
However, the COLUMN() is kind of tricky
Because of the fact that cell addresses (or references) are based on the old style of R1C1
=Column()
Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »
iframe in Excel (XLiFrame)
This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.
Formulas used to bring actual data is:
=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))
While formula used to bring header labels:
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: