Answering my own question here. Thanks to JvdV for putting me on the right path. In this answer I found what solved my question. I added this line before saving the excel file in my example code: ws.formula_attributes[‘E9’] = {‘t’: ‘array’, ‘ref’: “E9:E9”} This essentially sets the formula in cell ‘E9’ to be read as … Read more
Let me add two additional methods to the answer by @Harun24HR. Both options assume you don’t have headers as per your sample data. Option 1) : Dynamic Array Functions When one has access to dynamic array functions you may use the following: In C1: =UNIQUE(A1:A17) This UNIQUE function will spill an array of unique values … Read more
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
This works, even when there are middle names: =MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,” “,CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))+1,LEN(A2)) If you want everything BUT the last name, check out this answer. If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula. Note that it is only … Read more
You can use XL4 macros (Excel formula) to count up cells with different backcolor or even font colour in excel 🙂 See this LINK. For Font color the type_num is 24. And for backcolor we will use 63 Open the Name Manager Give a name. Say BackColor Type this formula in Refers To =GET.CELL(63,OFFSET(INDIRECT(“RC”,FALSE),-1,0)) and … Read more
use: =TEXTJOIN(“/”,,FILTERXML(“<a><b>”&SUBSTITUTE(LEFT(TEXT(A1,”d/m/yy”)&” “,FIND(” “,TEXT(A1,”d/m/yy”)&” “)-1),”/”,”</b><b>”)&”</b></a>”,”//b[“&{2,1,3}&”]”))+MID(TEXT(A1,”\ hh:mm AM/PM”),FIND(” “,TEXT(A1,”\ hh:mm AM/PM”))+1,99) and then format the output as desired. Note: I am US based so I had to reverse it in the demo. Another Note: If one is US based dealing with UK dates then change the d/m/yy to m/d/yy and it will work.
This will return the matching word or an error if no match is found. For this example I used the following. List of words to search for: G1:G7 Cell to search in: A1 =INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1))) Enter as an array formula by pressing Ctrl+Shift+Enter. This formula works by first looking through the list of words to find … Read more
Have a go with TEXTSPLIT() nested in REDUCE(): Formula in B1: =IFERROR(DROP(REDUCE(0,A1:A15,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{“|”,” (“})))),1,-1),””)
I was able to determine that: SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)) is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden. ISNUMBER(SEARCH(“Pear”,B2:B18))+0) is used to return an array of which cells contain “Pear”. If “Pear” is found, 1 is returned, else 0. … Read more
The way you are trying to do it is not possible. Chart data range has to have a fixed address. There is a way around this, and that’s using named ranges Put the number of rows you want in your data in a cell (e.g., E1) So, using your example, I put Number of Rows … Read more