worksheet-function - w3toppers.com

Get parent folder path from file path using cell formula

This works. =MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,”\”,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))+1,LEN(A1)))) The above was my original answer. Neil simplified the expression somewhat and posted this as a comment below: =LEFT(A1,FIND(“?”,SUBSTITUTE(A1,”\”,”?”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))) This takes advantage of the fact that ? is a forbidden character in paths so that “?” can safely be used instead of CHAR(1) as a placemark, thus improving readability a little bit. … Read more

How to extract text within a string of text

Here is a very flexible VBA answer using the regex object. What the function does is extract every single sub-group match it finds (stuff inside the parenthesis), separated by whatever string you want (default is “, “). You can find info on regular expressions here: http://www.regular-expressions.info/ You would call it like this, assuming that first … Read more

Excel Reference To Current Cell

Several years too late: Just for completeness I want to give yet another answer: First, go to Excel-Options -> Formulas and enable R1C1 references. Then use =CELL(“width”, RC) RC always refers the current Row, current Column, i.e. “this cell”. Rick Teachey’s solution is basically a tweak to make the same possible in A1 reference style … Read more

Using built-in spreadsheet functions in a script

Google Apps Script is a subset of JavaScript, spreadsheet functions are currently not supported. For example, if you want to create a function that returns today’s date you should write : function test_today(){ return new Date() }// note that this will eventually return a value in milliseconds , you’ll have to set the cell format … Read more

Fill non-contiguous blank cells with the value from the cell above the first blank

Select A1:A8. Press F5 to show the Goto dialog. Click Special ….. Select Blanks and click OK. That will select a noncontiguous range of blank cells. Then, without selecting anything else, type =A3 and press control+enter. That will enter an array formula in all the blank cells referring to the cell above it. Reselect A1:A8, … Read more

Round function in Excel, worksheet function vs VBA

It’s a known issue. The VBA Round() function uses Banker’s rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here: PRB: Round Function different in VBA 6 and Excel Spreadsheet The workaround proposed by Microsoft is to write a custom function to get the desired results. Banker’s rounding always rounds 0.5 to … Read more

Rounding in MS Access

Be careful, the VBA Round function uses Banker’s rounding, where it rounds .5 to an even number, like so: Round (12.55, 1) would return 12.6 (rounds up) Round (12.65, 1) would return 12.6 (rounds down) Round (12.75, 1) would return 12.8 (rounds up) Whereas the Excel Worksheet Function Round, always rounds .5 up. I’ve done … Read more

How to count up text of a different font colour in excel

You don’t need VBA for this but still if you want VBA Solution then you can go with any of the other two answers. 🙂 We can use Excel formula to find the Font Color of a cell. See this example. We will be using XL4 macros. Open the Name Manager Give a name. Say … Read more

Get the last non-empty cell in a column in Google Sheets

There may be a more eloquent way, but this is the way I came up with: The function to find the last populated cell in a column is: =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ) So if you combine … Read more

Excel formula to get cell color [duplicate]

As commented, just in case the link I posted there broke, try this: Add a Name(any valid name) in Excel’s Name Manager under Formula tab in the Ribbon. Then assign a formula using GET.CELL function. =GET.CELL(63,INDIRECT(“rc”,FALSE)) 63 stands for backcolor. Let’s say we name it Background so in any cell with color type: =Background Result: … Read more