If you work with a table in which there are several arrays, such as city names or sales values in them, until now you could only combine them by copying and pasting to the desired position in the worksheet. As of a few weeks ago, if you are a Microsoft 365 subscriber, you can also use two functions for working with dynamic arrays: VSTACK and HSTACK. They allow merging arrays which contributes to easier preparation of data for reporting.
When creating a report, there will sometimes be a need to rearrange rows and/or columns into a form suitable for reporting. Previously, you had to do this manually, and now there are two functions that can make the said job easier for you. They are named WRAPROWS and WRAPCOLUMNS. Let’s see how you can “wrap” the data in a way that suits you.
The TRANSPOSE function has been around for a very long time and allows you, when working with formulas containing arrays, to transpose them from row to column or vice versa. A similar effect can be obtained using the Paste Special option. Since recently, Excel offered to users through Microsoft 365 subscription, has functions TOROW and TOCOL with which we can easily convert rows into columns and columns into rows…
The TEXTJOIN function is one of the newer ones, until Office 2019 exclusively available to Microsoft 365 subscribers, and it allows joining multiple strings into one by specifying them and then defining delimiters and the option to ignore those that are empty. Since august, the TEXTSPLIT function appeared, and its purpose is to split strings. How to use this function you will find out in the “recipe” that follows…
In the “Excel Kitchenette” blog so far you could have met many text manipulation functions. As of August 2022 (unofficially), and officially as of a few weeks ago, Microsoft 365 subscribers have some new functions at their disposal that increase productivity and can help you more easily to retrieve individual parts of a given text, before or after the delimiter.