EXTRACT function: select data between defined tags
Create an EXTRACT function to extract data between defined beginning and end tags.
=EXTRACT(start string, end string, target cell/range)
USE CASE
While there are many use cases for this function, I see it as a way to facilitate “lightweight” cross-sheet referencing. This function would allow you to package data from one page in a single column, with tags around the data elements, then reference that single column on another page. In the target page, the tightly packed data can be accessed using the EXTRACT function.
For instance, if in your source sheet Column A contains “Cat” and Column B contains “Pizza”, you could concatenate the data in Column C (using a formula) so it looks like this:
<cA>Cat<\cA><cB>Pizza<\cB>
Then, you use a cross sheet reference to bring this column to your target sheet, into a column named “Source Data”. This is a simple scenario, but imagine you are sending a dozen or even a hundred column’s worth of data over.
In your target sheet, you would then be able to easily reference defined pieces of that data with the new EXTRACT function:
=EXTRACT(“<cA>”, “<\cA>”, [Source Data]@row)
The formula is saying, “In the column “Source Data” , retrieve the information between the tags “<cA>” and “<\cA>”.
A formula like this could really simplify complicated cross-sheet interactions and significantly lighten the computational load for big sheets that are slamming into the 25M barrier. This extraction can already be done with a series of nested LEFT/RIGHT/FIND functions, but it’s ugly and complicated.