嵌套SUBSTITUTE函数
你好所有的,
我需要帮助!!我正试图写一个公式，取代字母“a”与数字“1”，“b”与“2”，“c”与“3”等....单独的公式(例如)=SUBSTITUTE([Column3]1，“a”，“1”)很简单，但由于某种原因，我似乎不知道如何将它与其他字母和数字嵌套在一起。我已经试过了我能想到的所有不同的方法，但似乎都不管用。我的最终目标是“abc”在目标单元格中自动变为“123”。但是，我不想限制每个特定字母可以替换的次数。这样，如果我有“abchbacb”，它就会变成“1232132”。有什么想法?
谢谢!
thinkspi.com
评论
-
有一种方法可以使用VLOOKUP和MID公式的组合来实现。
首先，制作表格:
COL1__COL 2
一个________1
B________2
C________3
等等......
公式为:
= VLOOKUP(中期(文本单元1,1,1),[COL1] 1: [COL2] 3 2, false) + "
VLOOKUP(MID([TEXT CELL] 1,2,1)， [COL1]1:[COL2] 3,2, false) +
VLOOKUP(MID([TEXT CELL] 1,3,1)， [COL1]1:[COL2] 3,2, false) +
等等......
注意，你只需要放一次“”(这将生成一个字符串，并在前面的数字后面“放”另一个数字，而不是添加它们)。
你需要重复这个公式的次数和文本的长度一样多，因为每行只改变一个字母。
[TEXT CELL]1 -单元格的引用
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
类别
Nested IF formulas read from left to right and stop on the first true value. So if you have In Progress in one child row and On Hold in another child row, it will register a true value with the In Progress argument, output that, then stop evaluating. If you want On Hold to \"override\" In Progress, you would need to adjust the order in which your IFs are nested.<\/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":106419,"type":"question","name":"SUMIFS not working (referencing another sheet)","excerpt":"I'm struggling with getting a SUMIFS function to work properly that is referencing another sheet. So on my sheet with the function, I have it as: =SUMIFS({Total GL Detail Range 4}, {Total GL Detail Range 5}, Project8, {Total GL Detail Range 3}, Title@row) ...where the \"Total GL Detail\" is the other sheet referenced. Range…","categoryID":322,"dateInserted":"2023-06-14T13:17:37+00:00","dateUpdated":null,"dateLastComment":"2023-06-14T20:49:20+00:00","insertUserID":162357,"insertUser":{"userID":162357,"name":"EBooher","url":"https:\/\/community.smartsheet.com\/profile\/EBooher","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-14T20:58:21+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-15T01:08:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":15,"countViews":103,"score":null,"hot":3373533417,"url":"https:\/\/community.smartsheet.com\/discussion\/106419\/sumifs-not-working-referencing-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106419\/sumifs-not-working-referencing-another-sheet","format":"Rich","lastPost":{"discussionID":106419,"commentID":380651,"name":"Re: SUMIFS not working (referencing another sheet)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380651#Comment_380651","dateInserted":"2023-06-14T20:49:20+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-15T01:08:28+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-14T20:08:57+00:00","dateAnswered":"2023-06-14T18:51:01+00:00","acceptedAnswers":[{"commentID":380616,"body":"
Ok. I just had someone else earlier this week have the same issue. Even after converting all of the IDs to text it still wasn't working. Lets try converting everything on the source sheet to numbers. Take that LEFT formula you have that strips the ID out and wrap it in a VALUE function.<\/p>
=VALUE(LEFT(.......................))<\/p>
<\/p>
Then try the COUNTIFS on the ID again, but instead of referencing the helper cell in the formula sheet that we created, reference the linked cell that houses the numerical value.<\/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":106432,"type":"question","name":"Form drop down selection query","excerpt":"Hi, Is it possible to create a form based off a sheet where you can select an entry already in the primary column which would then allow you to update specific other columns? I would like to have a form where I can select an entry in T Number column, then update Cal Due, In Cal checkbox and comments columns. Is this…","categoryID":322,"dateInserted":"2023-06-14T15:50:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-14T17:40:38+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-14T20:50:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-15T04:00:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":55,"score":null,"hot":3373526460,"url":"https:\/\/community.smartsheet.com\/discussion\/106432\/form-drop-down-selection-query","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106432\/form-drop-down-selection-query","format":"Rich","tagIDs":[204],"lastPost":{"discussionID":106432,"commentID":380583,"name":"Re: Form drop down selection query","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380583#Comment_380583","dateInserted":"2023-06-14T17:40:38+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-15T04:00:26+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\/RUR0T5ZW0YL1\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-14T16:01:15+00:00","dateAnswered":"2023-06-14T15:59:16+00:00","acceptedAnswers":[{"commentID":380524,"body":"
