Another FILTERXML hack. Suppose you have following string of words in cell A2 Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin You want to reverse this string of words and want following output. Berlin, Detroit, Miami, Washington, Delhi, Paris, London, Moscow FILTERXML will come to your rescue. You can use following formula for this =TEXTJOIN(",…
Category: Tips and Tricks
Tips & Tricks 183 – Using FILTERXML to extract nth word from front and back
Suppose you have following string Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin And you want to retrieve nth word from front. You can use following FILTERXML formula for this =FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]") Where N need to be replaced with the word number which you want. Hence, if you wanted 2nd word, then replace that with…
Tips & Tricks 182 – Determine Quarter for Fiscal Year
Few countries follow different quarter other than Q1 from Jan-Mar and Q2 for Apr-Jun. In case of Jan-Mar as Q1, formula is simple (if cell A2 is date) =ROUNDUP(MONTH(A2)/3,0) This will give result as 1, 2, 3 & 4 for the quarters. If you want, you can concatenate "Q" in the formula to show Q1,…
Tips & Tricks 181 – Insert Sequence / Serial Numbers in a Pivot Table
Sometimes, it is important to show sequence numbers such as 1, 2, 3…..and so on. If you use database technologies, this is known as index column. 1, 2, 3…..are also used to denote rank. Suppose you have a pivot table which is sorted in descending order for the value. For the sake of showing ranking, you…
Split a Column Dynamically – Power Query – Tips & Tricks 6
Power Query Split a Column Dynamically is a very important tool for PQ practitioners. You can use this to split your column into variable number of columns. This removes limitation of splitting a column on the basis of first time split if result columns are more than first time split columns.
Tips & Tricks 180 – Determine the date given the weeknumber
Given a year and week number, below formula finds the Sunday as the date. =MAX(DATE(F2,1,1),DATE(F2,1,1)-WEEKDAY(DATE(F2,1,1),1)+1+(G2-1)*7) Where F2 has the year and G2 has the week number. For week 1, this would always give 1-Jan as the date.
Tips & Tricks 179 – Retrieve Sheet Name in Excel Online
We can easily retrieve sheet name by following – Tips & Tricks 34 – Get Sheet Name through Formula But the formula depends upon CELL function. Excel Online i.e. web version of Excel doesn't support CELL function. It doesn't support VBA as well. Hence, we need to find an alternative to extract sheet name through…
Test for a Number – Power Query – Tips & Tricks 5
Test for a Number is a very important topic in Power Query Suppose, you have a column of values and you need to test whether the value contained is a number or not.
Change Index at discontinuity – Power Query – Tips & Tricks 4
Power Query – Change Index at each discontinuity. Sometimes, you need to insert index but change is needed at when value in left column changes
Inserting a Sequence 1,2,3,1,2,3,1,2,3…..- Power Query – Tips & Tricks 3 –
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,2,3,4,1,2,3,4,1,2,3,4