Sign in to submit new ideas and vote
Get Started

EXTRACT function: select data between defined tags

Lucas Rayala
Lucas Rayala ✭✭✭✭✭

Create an EXTRACT function to extract data between defined beginning and end tags.

=EXTRACT(start string, end string, target cell/range)

USE CASE

虽然有很多用例这个函数,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:

Cat<\cA>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>”, [Source Data]@row)

The formula is saying, “In the column “Source Data” , retrieve the information between the tags “” 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.

2
Up
2 votes

Idea Submitted·Last Updated

Comments

2023-05-27T11:20:11.000+00:00","expand":["all","-body"],"excludeHiddenCategories":true,"type":"Idea"},"discussions":[{"discussionID":106863,"type":"idea","name":"Add friends\/coworkers within the community","excerpt":"I would love to be able to add and follow friends within this community- is this already possible? Does this currently exist?","categoryID":348,"dateInserted":"2023-06-24T00:04:38+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T08:41:17+00:00","insertUserID":146478,"insertUser":{"userID":146478,"name":"malbers","title":"","url":"https:\/\/community.smartsheet.com\/profile\/malbers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/UWZGZGP18PK8\/nZFAGSNFMCT3V.jpg","dateLastActive":"2023-06-26T17:54:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":3,"hot":3375335455,"url":"https:\/\/community.smartsheet.com\/discussion\/106863\/add-friends-coworkers-within-the-community","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106863\/add-friends-coworkers-within-the-community","format":"Rich","tagIDs":[363],"lastPost":{"discussionID":106863,"commentID":382214,"name":"Re: Add friends\/coworkers within the community","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382214#Comment_382214","dateInserted":"2023-06-26T08:41:17+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-26T16:24:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Ideas & Feature Requests","url":"https:\/\/community.smartsheet.com\/categories\/ideas-%26-feature-requests"},{"name":"Community Platform Feedback","url":"https:\/\/community.smartsheet.com\/categories\/community-platform-feedback"}],"groupID":null,"statusID":10000,"attributes":{"idea":{"statusNotes":null,"statusID":10000,"status":{"name":"Idea Submitted","state":"open"},"type":"up"}},"status":{"statusID":10000,"name":"Idea Submitted","state":"open","recordType":"discussion","recordSubType":"ideation"},"bookmarked":false,"unread":false,"category":{"categoryID":348,"name":"Community Platform Feedback","url":"https:\/\/community.smartsheet.com\/categories\/community-platform-feedback","allowedDiscussionTypes":[]},"reactions":[{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":3}],"tags":[{"tagID":363,"urlcode":"Idea Submitted","name":"Idea Submitted"}]},{"discussionID":106789,"type":"idea","name":"Limiting attachment access by license","excerpt":"I have run into a situation where I have confidential documents attached to a worksheet row that need to be available to certain personnel. These same worksheet rows are also accessed by personnel that should NOT have access to these documents. I already have hidden the attachment column, and several other columns that…","categoryID":349,"dateInserted":"2023-06-22T17:27:25+00:00","dateUpdated":"2023-06-22T17:28:26+00:00","dateLastComment":"2023-06-26T18:06:18+00:00","insertUserID":162646,"insertUser":{"userID":162646,"name":"karlj","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/karlj","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T17:27:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162646,"lastUserID":161565,"lastUser":{"userID":161565,"name":"Courtney S.","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:59:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":2,"hot":3375258823,"url":"https:\/\/community.smartsheet.com\/discussion\/106789\/limiting-attachment-access-by-license","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106789\/limiting-attachment-access-by-license","format":"Rich","tagIDs":[268,363],"lastPost":{"discussionID":106789,"commentID":382382,"name":"Re: Limiting attachment access by license","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382382#Comment_382382","dateInserted":"2023-06-26T18:06:18+00:00","insertUserID":161565,"insertUser":{"userID":161565,"name":"Courtney S.","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:59:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Ideas & Feature Requests","url":"https:\/\/community.smartsheet.com\/categories\/ideas-%26-feature-requests"},{"name":"Smartsheet Product Feedback & Ideas","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-%26-ideas"}],"groupID":null,"statusID":10000,"attributes":{"idea":{"statusNotes":null,"statusID":10000,"status":{"name":"Idea Submitted","state":"open"},"type":"up"}},"status":{"statusID":10000,"name":"Idea Submitted","state":"open","recordType":"discussion","recordSubType":"ideation"},"bookmarked":false,"unread":false,"category":{"categoryID":349,"name":"Smartsheet Product Feedback & Ideas","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-%26-ideas","allowedDiscussionTypes":[]},"reactions":[{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":2}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":363,"urlcode":"Idea Submitted","name":"Idea Submitted"}]},{"discussionID":106787,"type":"idea","name":"Print a Smartsheet form- to MS Word and PDF","excerpt":"It would be nice to have the ability to print a Smartsheet form created to MS Word and PDF (form view layout, not smartsheet view layout) after someone has submitted the form.","categoryID":349,"dateInserted":"2023-06-22T16:46:26+00:00","dateUpdated":"2023-06-22T16:52:12+00:00","dateLastComment":"2023-06-26T18:34:44+00:00","insertUserID":162643,"insertUser":{"userID":162643,"name":"TomGSB","url":"https:\/\/community.smartsheet.com\/profile\/TomGSB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T00:12:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":161565,"lastUser":{"userID":161565,"name":"Courtney S.","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:59:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":14,"score":3,"hot":3375258370,"url":"https:\/\/community.smartsheet.com\/discussion\/106787\/print-a-smartsheet-form-to-ms-word-and-pdf","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106787\/print-a-smartsheet-form-to-ms-word-and-pdf","format":"Rich","tagIDs":[204,363],"lastPost":{"discussionID":106787,"commentID":382386,"name":"Re: Print a Smartsheet form- to MS Word and PDF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382386#Comment_382386","dateInserted":"2023-06-26T18:34:44+00:00","insertUserID":161565,"insertUser":{"userID":161565,"name":"Courtney S.","url":"https:\/\/community.smartsheet.com\/profile\/Courtney%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:59:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Ideas & Feature Requests","url":"https:\/\/community.smartsheet.com\/categories\/ideas-%26-feature-requests"},{"name":"Smartsheet Product Feedback & Ideas","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-%26-ideas"}],"groupID":null,"statusID":10000,"attributes":{"idea":{"statusNotes":null,"statusID":10000,"status":{"name":"Idea Submitted","state":"open"},"type":"up"}},"status":{"statusID":10000,"name":"Idea Submitted","state":"open","recordType":"discussion","recordSubType":"ideation"},"bookmarked":false,"unread":false,"category":{"categoryID":349,"name":"Smartsheet Product Feedback & Ideas","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-%26-ideas","allowedDiscussionTypes":[]},"reactions":[{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":3}],"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":363,"urlcode":"Idea Submitted","name":"Idea Submitted"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&dateInserted=%3E2023-05-27T11%3A20%3A11%2B00%3A00&type%5B0%5D=Idea&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser","prevURL":null,"currentPage":1,"total":138,"limit":3},"title":"Trending Ideas","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">