帮助处理单元间的数据

WinaHath
WinaHath ✭✭
编辑06/06/23 公式和函数

我需要将字符复制到由列Department 1、Department 2、Department 3、Department 4和Department 5的破折号分隔的文本字符串的左侧,以单元格Department Code在同一行中,但不连接,如果没有数据返回空白。

来自输入表单的响应一次只填充一列,因此不需要连接数据。

我需要配方来实现这一点,请尽快。

我向ChatGPT寻求帮助,这就是我得到的,但当我把它复制到我的细胞时,它是无法解析的。


=IF(OR(ISBLANK([Department AC]@row), ISBLANK([Department AG]@row), ISBLANK([Department CA]@row), ISBLANK([Department ES/SP]@row)), "", IFERROR(LEFT(TRIM([Department AC] 1000 - 8010]@row), FIND(",", TRIM([Department AC] 1000 - 8010]@row))) & IFERROR(LEFT(TRIM([Department AC]@row)), FIND(",", TRIM([Department AC]@row))) & IFERROR(LEFT(TRIM([Department AC]@row))) & IFERROR(LEFT(TRIM([Department AC]@row))TRIM([Department AG]@row)) - 1), TRIM([Department AG]@row)) & IFERROR(左(TRIM([Department CA]@row), FIND(",", TRIM([Department CA]@row)) - 1), TRIM([Department CA]@row)) & IFERROR(左(TRIM([Department ES/SP]@row)), FIND(",", TRIM([Department ES/SP]@row)) - 1), TRIM([Department ES/SP]@row)))



谢谢你!

最佳答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    我提供的公式不是你之前发布的公式。你用JOIN/COLLECT发布的公式在第一个COLLECT中缺少一个范围。


    要获得非空白单元格,只需要JOIN/COLLECT,不需要LEFT和FIND函数。

    =JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row:[Last Dept]@row, @cell <> ""))

    thinkspi.com

«1

答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    你有文本字符串的示例或截图吗?看起来ChatGPT是给你一个Excel公式,甚至不像它可能是有效的。


    这与你最有可能得到的结果相似:

    =LEFT(JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row, @cell <> "")), FIND("-", JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row, [1st Dept]@row, @cell <> ""))) - 1)


    基本上我们用这个来获取非空白的单元格:

    JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row:[Last Dept]@row, @cell <> ""))


    然后我们将其放入LEFT函数中以拉入左侧字符,并使用FIND函数来定位破折号,该破折号告诉我们要拉入多少字符。

    thinkspi.com

  • 非常感谢您的回复!!!!

    所以我有这些部门组:部门1000-8010,部门AC,部门AG,部门CA,部门ES/SP。以下是文本字符串示例:

    部门1000-8010:3285 -数字取证和网络安全

    部门AC: AC9060 -高尔夫营-高级

    部门AG: AG0160 - Kappa Sigma兄弟会

    部门CA: CA0174 - PGA夏季经验

    部门SP: SP0266 -律师助理研究


    我需要从这些列的文本字符串中提取字母数字代码,并用该代码填充Department code列。数据由请求者在输入表单上输入,每个请求只选择一个部门,因此永远不会出现2个或更多部门列有数据的情况。

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    所以"部门1000-8010:3285 -数字取证和网络安全"应该在单元格里,还是"3285 -数字取证和网络安全"?


    上面的公式(一旦将列名调整为实际的列名)将输出

    “部门1000-8010:3285”

    “3285”


    这取决于细胞中的物质。

    thinkspi.com

  • 它应该输出3285

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    好的。那么细胞里到底有什么呢?单元格是否实际包含“Department 1000-8010:”,还是仅仅使用列名作为示例?

    thinkspi.com

  • WinaHath
    WinaHath ✭✭
    编辑06/06/23

    3285 -数字取证和网络安全-是该大学的一个系

    Department 1000-8010是列名称,上面的代码位于下拉列表中。

    与部门AG相同,这是一个列标题和

    AG0160 - Kappa Sigma博爱是一个部门代码在下拉列表中。


    所以对于这两个例子-我只需要alpha数字或数字部门代码输出到列部门代码。



    谢谢你!

    W

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    好的。所以“部门1000-8010”不会在单元格中?如果是这种情况,那么我上面提供的LEFT公式应该适用于您,一旦您将列名更新为您在工作表中拥有的列名。

    thinkspi.com

  • 保罗,

    对不起,我很笨,我可以做一个范围从同一行(即[第一部门]@行:[最后部门]@行)?如果没有,那么我是否需要以[Dept1]@row,[Dept2]@row,[Dept 3]@row等方式输入每个部门?

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    你可以用我在上面的例子中使用的方法。您只需要确保公式中的列名与工作表中使用的列名相同。

    thinkspi.com

  • 早上好,保罗!

    =LEFT(JOIN(COLLECT([Department 1000-8010]@row:[Department ZZ]@row, @cell <> "")), FIND("-"), JOIN(COLLECT([Department 1000-8010]@row:[Department ZZ]@row, [Department 1000-8010]@row:[Department ZZ]@row, @cell <> ""))) - 1)

    我得到一个#INCORRECT ARGUMENT SET

    谢谢!

    Wina

  • 如果不是从范围收集到一个单元格,我只是想从单元格收集数据,不是空白的,并输出到单元格?我不再需要找到文本字符串左边的字符。我只是想收集数据,从一个范围的单元格,不是空白的。

    我试过这个,但它没有工作:

    =INDEX(COLLECT([部门代码]@row:[部门ZZ]@row,[部门代码]@row:[部门ZZ]@row, @cell ""))

    有什么问题吗?

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    JOIN/COLLECT是将非空白单元格拉入的方法。您的第一个COLLECT函数缺少一个范围。两者都应该有两个函数(如第二个COLLECT)。

    thinkspi.com

  • 当我尝试您发送的公式时,我一直得到#不正确的参数设置。因此,我没有尝试使用LEFT函数来查找alpha数字代码,而是将代码分成单独的列。现在我只需要从一个范围内的非空白单元格收集数据。

  • @Paul新来的-谢谢你的帮助,但是我的配方问题还没有解决。我想如果你有我的表格的截图,你会更好地理解我想要实现的,它真的很简单。

    image.png

    我如何收集代码3000(部门代码栏)并将其复制到部门代码栏?如果其他细胞有数据,也会这样吗?目标不是加入他们,而只是收集或复制到专栏部门代码。

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    我提供的公式不是你之前发布的公式。你用JOIN/COLLECT发布的公式在第一个COLLECT中缺少一个范围。


    要获得非空白单元格,只需要JOIN/COLLECT,不需要LEFT和FIND函数。

    =JOIN(COLLECT([1st Dept]@row:[Last Dept]@row, [1st Dept]@row:[Last Dept]@row, @cell <> ""))

    thinkspi.com

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
You da man, @Paul Newcome<\/a>! That works perfectly. Thank you!!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106782,"type":"question","name":"IF cell contains specific letter then return value","excerpt":"I'd like to return a value of 120\/208 in Service Volts for Xfmr# ending in E value of 277\/480 for Xfmr# ending in X value of 120\/240 for Xfmr# ending with no letter Thanks","categoryID":322,"dateInserted":"2023-06-22T16:38:09+00:00","dateUpdated":"2023-06-22T16:45:09+00:00","dateLastComment":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T13:18:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":158055,"lastUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T13:18:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3374911260,"url":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","format":"Rich","lastPost":{"discussionID":106782,"commentID":381817,"name":"Re: IF cell contains specific letter then return value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381817#Comment_381817","dateInserted":"2023-06-22T18:12:51+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T13:18:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RLY9EZQ8E5Y9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:08:50+00:00","dateAnswered":"2023-06-22T17:35:06+00:00","acceptedAnswers":[{"commentID":381799,"body":"

Hi @mromaire<\/a>,<\/p>

Try this!<\/p>

=IF(RIGHT([xfmr#]@row, 1) = \"X\", \"277\/480\", IF(RIGHT([xfmr#]@row, 1) = \"E\", \"120\/208\", \"120\/240\"))<\/p>

Hope that helps!<\/p>

BRgds,<\/p>

-Ray<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106771,"type":"question","name":"SUMIFS Formula for Column with Various Possible Returns","excerpt":"Hi there! I am a big Excel user switching to Smartsheet and need help with a SUMIFS formula. In Excel, the formula was: =SUM(SUMIFS('Outside Counsel Invoices'!$G:$G,'Outside Counsel Invoices'!$M:$M,\"FY23\",'Outside Counsel Invoices'!$D:$D,{\"=7607\",\"=7607*\"})) Since Wildcards (*) are not used in Smartsheet, I am struggling…","categoryID":322,"dateInserted":"2023-06-22T14:38:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T18:57:32+00:00","insertUserID":162633,"insertUser":{"userID":162633,"name":"BuckeyeGirl72","url":"https:\/\/community.smartsheet.com\/profile\/BuckeyeGirl72","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T19:01:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T13:22:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":33,"score":null,"hot":3374907944,"url":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106771\/sumifs-formula-for-column-with-various-possible-returns","format":"Rich","lastPost":{"discussionID":106771,"commentID":381826,"name":"Re: SUMIFS Formula for Column with Various Possible Returns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381826#Comment_381826","dateInserted":"2023-06-22T18:57:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T13:22:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:38:09+00:00","dateAnswered":"2023-06-22T17:53:03+00:00","acceptedAnswers":[{"commentID":381809,"body":"

It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.<\/p>

SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS(\"7607\", @cell)))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势