Archives: Texts and Strings

Count how many times letter found

Found this in an old file while doing some digital cleaning This will show you how you can easily count how many times a letter (or any string) into a cell. So the main formula doing that is … =LEN(I12)-LEN(SUBSTITUTE(I12,”o”,””)) Of course, this assumes you are looking to find how many the letter “O” is • Read More »

Multiple lines in cell using functions

You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post). And you could have already know that if you want to have multiple lines inside • Read More »

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, • Read More »

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: