This one is tested and does work (based on Brad’s original post): =RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,” “,”|”, LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) If your original strings could contain a pipe “|” character, then replace both in the above with some other character that won’t appear in your source. (I suspect Brad’s original was broken because an unprintable character was removed in … Read more
First, you need to know / understand that any number is a potential date / time for Excel and that Excel knows only datetime (and not date and time separately). Here is why: anything before the decimals-placeholder are the days since December 31, 1899. So, the number 42,000.00 is also a date. It is the … Read more
UPDATE: You can do this now automatically with Excel 2013. I’ve created this as a new answer because my previous answer actually solves a slightly different problem. If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option ‘Add this data to … Read more
There is definitively no way to do this with standard formulas. However, a crazy sort of answer can be found here. It still avoids VBA, and it will allow you to get your result dynamically. First, make the formula that will generate your formula, but don’t add the = at the beginning! Let us pretend … Read more
I think I get what you mean. Let’s say for example you want the right-most \ in the following string (which is stored in cell A1): Drive:\Folder\SubFolder\Filename.ext To get the position of the last \, you would use this formula: =FIND(“@”,SUBSTITUTE(A1,”\”,”@”,(LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””)))/LEN(“\”))) That tells us the right-most \ is at character 24. It does this by … Read more
Many ways: If one has the new Dynamic array formulas: =FILTER(C:C,(A:A=J1)*(B:B=J2)) If not then: Dealing with Number returns: If your return values are numbers and the match is unique(there is only one John Doe in the data) or you want to sum the returns if there are multiples, then Using SUMIFS is the quickest method. … Read more
This TextJoinIfs user-defined-function (aka UDF) provides basic TEXTJOIN functionality to Excel 2003 – 2013 versions as well as expanded functionality for all versions by adding optional error control, uniqueness, sorting and a paramarray of conditions for easy criteria. This TextJoinIfs UDF code belongs in a public module code sheet; e.g. Book1 – Module1 (code). Option … Read more
Excel does not have any way to do this. The result of a formula in a cell in Excel must be a number, text, logical (boolean) or error. There is no formula cell value type of “empty” or “blank”. One practice that I have seen followed is to use NA() and ISNA(), but that may … Read more
Ok, I have two ideas for you. Hopefully one of them will get you where you need to go. Note that the first one ignores the request to do this as a formula since that solution is not pretty. I figured I make sure the easy way really wouldn’t work for you ;^). Use the … Read more
Good question, and I looked it up… In short: Use =Cells(x,y).Formula2 instead of =Cells(x,y).Formula Explaination: The @ that shows is called the implicit intersection operator. From MS docs: Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only … Read more