登录提交新的想法和投票
开始

EXTRACT函数:在定义的标签之间选择数据

创建EXTRACT函数以提取定义的开始和结束标记之间的数据。

=EXTRACT(开始字符串,结束字符串,目标单元格/范围)

用例

虽然这个函数有很多用例,但我认为它是一种促进“轻量级”交叉表引用的方法。该函数将允许您将来自一个页面的数据打包到单个列中,并在数据元素周围加上标记,然后在另一个页面上引用该单个列。在目标页面中,可以使用EXTRACT函数访问紧密打包的数据。

例如,如果在你的源表中,A列包含“Cat”,B列包含“Pizza”,你可以将C列中的数据连接起来(使用公式),看起来像这样:

< cA >猫< \ cA > < cB >比萨< \ cB >

然后,使用交叉表引用将该列带到目标表中,即名为“Source Data”的列中。这是一个简单的场景,但是想象一下您正在发送十几列甚至一百列的数据。

在目标工作表中,您可以使用新的EXTRACT函数轻松引用已定义的数据片段:

=EXTRACT(" ", " <\cA> ",[源数据]@row)

公式表示,在“源数据”列中,检索标签“”和“<\cA>”之间的信息。

这样的公式确实可以简化复杂的跨片相互作用,并显著减轻撞向25米屏障的大片的计算负荷。这种提取已经可以通过一系列嵌套的LEFT/RIGHT/FIND函数来完成,但它既丑陋又复杂。

2
2票

想法提交·最后一次更新

评论

2023-05-13T14:29:03.000+00:00","expand":["all","-body"],"excludeHiddenCategories":true,"type":"Idea"},"discussions":[{"discussionID":106271,"type":"idea","name":"Print Dynamic View","excerpt":"Print or export the Dynamic View in order to distribute it to students and faculty.","categoryID":349,"dateInserted":"2023-06-09T14:39:25+00:00","dateUpdated":"2023-06-09T14:48:13+00:00","dateLastComment":"2023-06-12T21:23:49+00:00","insertUserID":158793,"insertUser":{"userID":158793,"name":"megarceau","url":"https:\/\/community.smartsheet.com\/profile\/megarceau","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:37:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":160129,"lastUser":{"userID":160129,"name":"vdemattei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/vdemattei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T21:41:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":14,"score":2,"hot":3372927794,"url":"https:\/\/community.smartsheet.com\/discussion\/106271\/print-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106271\/print-dynamic-view","format":"Rich","tagIDs":[363,443,445],"lastPost":{"discussionID":106271,"commentID":380212,"name":"Re: Print Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380212#Comment_380212","dateInserted":"2023-06-12T21:23:49+00:00","insertUserID":160129,"insertUser":{"userID":160129,"name":"vdemattei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/vdemattei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T21:41:37+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":363,"urlcode":"Idea Submitted","name":"Idea Submitted"},{"tagID":443,"urlcode":"dynamic-view","name":"Dynamic View"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}]},{"discussionID":106267,"type":"idea","name":"Have Smartsheet auto include a generated PDF in the notification email","excerpt":"Hello! Is there way to have Smartsheet auto include a generated PDF in the notification email it sends out once a form is filled out and submitted? I'm still relatively new to Smartsheet and I currently have the automation set up to generate a document with each form submission and send an email to a specified email…","categoryID":349,"dateInserted":"2023-06-09T14:04:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-12T21:02:34+00:00","insertUserID":155580,"insertUser":{"userID":155580,"name":"Emilie","url":"https:\/\/community.smartsheet.com\/profile\/Emilie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T17:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":160129,"lastUser":{"userID":160129,"name":"vdemattei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/vdemattei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T21:41:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":45,"score":2,"hot":3372926830,"url":"https:\/\/community.smartsheet.com\/discussion\/106267\/have-smartsheet-auto-include-a-generated-pdf-in-the-notification-email","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106267\/have-smartsheet-auto-include-a-generated-pdf-in-the-notification-email","format":"Rich","tagIDs":[363],"lastPost":{"discussionID":106267,"commentID":380203,"name":"Re: Have Smartsheet auto include a generated PDF in the notification email","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380203#Comment_380203","dateInserted":"2023-06-12T21:02:34+00:00","insertUserID":160129,"insertUser":{"userID":160129,"name":"vdemattei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/vdemattei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T21:41:37+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":363,"urlcode":"Idea Submitted","name":"Idea Submitted"}]},{"discussionID":106300,"type":"idea","name":"EXTRACT function: select data between defined tags","excerpt":"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…","categoryID":349,"dateInserted":"2023-06-10T03:57:52+00:00","dateUpdated":"2023-06-10T05:11:38+00:00","dateLastComment":"2023-06-11T02:08:13+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-12T16:48:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":129958,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-12T16:48:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":2,"hot":3372821765,"url":"https:\/\/community.smartsheet.com\/discussion\/106300\/extract-function-select-data-between-defined-tags","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106300\/extract-function-select-data-between-defined-tags","format":"Rich","tagIDs":[363],"lastPost":{"discussionID":106300,"commentID":379996,"name":"Re: EXTRACT function: select data between defined tags","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379996#Comment_379996","dateInserted":"2023-06-11T02:08:13+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-06-12T16:48:48+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":363,"urlcode":"Idea Submitted","name":"Idea Submitted"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&dateInserted=%3E2023-05-13T14%3A29%3A03%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":152,"limit":3},"title":"Trending Ideas","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">