英文:
Strip trailing slashes from a string in Excel
问题
以下是翻译好的部分：
在Excel中有多行数据。以下是示例模式：
/xxx/yyy/zzz////
/xxx/yyy/zzz//
/xxx/yyy/zzz//////
/xxx/yyy/zzz
当斜杠被移除后，输出将如下所示：
/xxx/yyy/zzz
/xxx/yyy/zzz
/xxx/yyy/zzz
/xxx/yyy/zzz
请告诉我在Excel中用于去除斜杠的公式。最后一个示例模式不包含任何斜杠。谢谢！
我尝试了这个公式，但它不起作用 -
=IF(RIGHT(A1,1)="/",LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1),A1)
英文:
There are multiple rows in excel. And here is the sample pattern:
/xxx/yyy/zzz////
/xxx/yyy/zzz//
/xxx/yyy/zzz//////
/xxx/yyy/zzz
When the slashes are removed the output will look as follows:
/xxx/yyy/zzz
/xxx/yyy/zzz
/xxx/yyy/zzz
/xxx/yyy/zzz
Please let me know the Excel formula to strip the slashes. The last sample pattern doesn't contain any slashes. Thanks!
I tried this formula, and it dosn`t work -
=IF(RIGHT(A1,1)="/",LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1),A1)
答案1
得分: 7
尝试使用<kbd>TEXTSPLIT( )</kbd>和<kbd>TEXTJOIN( )</kbd>
• 在单元格B1中使用的公式：
="/"&TEXTJOIN("/",,TEXTSPLIT(A1,"/",,1))
编辑:
使用<kbd>TEXTJOIN( )</kbd>，<kbd>TAKE( )</kbd>和<kbd>TEXTSPLIT( )</kbd>
• 在单元格B1中使用的公式：
=TEXTJOIN("/",0,TAKE(TEXTSPLIT(A1,"/"),,4))
或者，
• 在单元格C1中使用的公式：
=TEXTJOIN("/",0,TAKE(TEXTSPLIT(A1,,"/"),4))
或者，如果您喜欢与DAF一起使用
• 在单元格B1中：
=MAP(A1:A4,LAMBDA(m,TEXTJOIN("/",0,TAKE(TEXTSPLIT(m,,"/"),4))))
注意： 我认为为第二种方法添加一个注意事项是有意义的。因为我假设在第4次出现/之前的任何内容都需要保留，其余内容都会被删除，如果没有第4次出现则保持原样。
这是我的建议，不能再好了。使用<kbd>TEXTBEFORE( )</kbd>
• 在单元格B1中使用的公式：
=TEXTBEFORE(A1&"/","/",4)
或者，扩展到多行的情况：
=TEXTBEFORE(A1:A4&"/","/",4)
由于我不知道您的Excel版本，我添加了一个适用于Excel 2010+及以后版本的备用版本。
• 在单元格B1中使用的公式：
=LEFT(SUBSTITUTE(A1&"/","/","@",4),
FIND("@",SUBSTITUTE(A1&"/","/","@",4))-1)
英文:
Try using <kbd>TEXTSPLIT( )</kbd> & <kbd>TEXTJOIN( )</kbd>
• Formula used in cell B1
="/"&TEXTJOIN("/",,TEXTSPLIT(A1,"/",,1))
Edit:
Using <kbd>TEXTJOIN( )</kbd> <kbd>TAKE( )</kbd> & <kbd>TEXTSPLIT( )</kbd>
• Formula used in cell B1
=TEXTJOIN("/",0,TAKE(TEXTSPLIT(A1,"/"),,4))
Or,
• Formula used in cell C1
=TEXTJOIN("/",0,TAKE(TEXTSPLIT(A1,,"/"),4))
Or, if you like to Spill with DAF
• in cell B1
=MAP(A1:A4,LAMBDA(m,TEXTJOIN("/",0,TAKE(TEXTSPLIT(m,,"/"),4))))
Caveat: I think adding a caveat makes sense for the second approaches. Because I have assumed anything before the 4th occurrence of / needs to stay and rest is removed, if there is no 4th one it should be as is.
This is my two cents, can't be better than this. Using <kbd>TEXTBEFORE( )</kbd>
• Formula used in cell B1
=TEXTBEFORE(A1&"/","/",4)
Or, a spilled one:
=TEXTBEFORE(A1:A4&"/","/",4)
Since I am not aware of your Excel Version, adding an alternate version, which works for Excel 2010+ onwards.
• Formula used in cell B1
=LEFT(SUBSTITUTE(A1&"/","/","@",4),
FIND("@",SUBSTITUTE(A1&"/","/","@",4))-1)
答案2
得分: 4
如果您的模式确实代表性，那么
=LEFT(A1,FIND("//",A1&"//")-1)
英文:
If your pattern is truly representative, then
=LEFT(A1,FIND("//",A1&"//")-1)
答案3
得分: 3
移除字符末尾的重复出现
=LET(data,A2:A11,ch,"/",dch,"@",es,"",od,"",
BYROW(data,LAMBDA(br,
IF(LEN(br)=0,es,LET(
rd,REDUCE(br,SEQUENCE(LEN(br)),LAMBDA(rr,r,LET(
ri,RIGHT(rr,1),
IF(ri=dch,rr,IF(ri=ch,LEFT(rr,LEN(rr)-1),rr&dch))))),
IF(LEN(rd)=0,od,LEFT(rd,LEN(rd)-1)))))))
1: https://i.stack.imgur.com/s6UZM.jpg
英文:
Remove Trailing Occurrences of a Character
=LET(data,A2:A11,ch,"/",dch,"@",es,"",od,"",
BYROW(data,LAMBDA(br,
IF(LEN(br)=0,es,LET(
rd,REDUCE(br,SEQUENCE(LEN(br)),LAMBDA(rr,r,LET(
ri,RIGHT(rr,1),
IF(ri=dch,rr,IF(ri=ch,LEFT(rr,LEN(rr)-1),rr&dch))))),
IF(LEN(rd)=0,od,LEFT(rd,LEN(rd)-1)))))))
答案4
得分: 3
以下是翻译好的部分：
如果您特别询问的是结尾的斜杠，那么您可以尝试以下方法：
公式在B1单元格中：
=MAP(A1:A6,LAMBDA(x,TEXTBEFORE(x,TEXTAFTER(x,TEXTSPLIT(x,"/",,1),-1),-1)))
或者，稍微严格一点（以防您只有斜杠）：
=MAP(A1:A7,LAMBDA(x,LEFT(x,@TOCOL(FIND(REPT("/",SEQUENCE(LEN(x),,LEN(x)-1,-1))&"@",x&"@")-1,3))))
英文:
Are you specifically asking for just the trailing forward slashes? If so, you may want to try:
Fomula in B1:
=MAP(A1:A6,LAMBDA(x,TEXTBEFORE(x,TEXTAFTER(x,TEXTSPLIT(x,"/",,1),-1),-1)))
Or, a little more strict (in case you have just forward slashes:
=MAP(A1:A7,LAMBDA(x,LEFT(x,@TOCOL(FIND(REPT("/",SEQUENCE(LEN(x),,LEN(x)-1,-1))&"@",x&"@")-1,3))))
答案5
得分: 0
"The last sample pattern doesn't contain any slashes."
Suppose the last cell is A4
=LEFT(A1;LEN($A$4))
英文:
"The last sample pattern doesn't contain any slashes."
Suppose the last cell is A4
=LEFT(A1;LEN($A$4))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论