英文:
I want to take clear phone numbers in excel
问题
我有电话号码；我需要去掉额外的字符，只留下数字。
不知道，我尝试了所有方法，查看了视频和其他资源。
如果我的问题很愚蠢，对不起。
我从未在Excel表格中使用过公式。
英文:
I have phone numbers; I need to get rid of the extra characters and leave only numbers.
idk i tried all, checked video and other.
sorry if my question stupid
I never used formulas in excel sheets.
答案1
得分: 3
尝试使用<kbd>REDUCE( )</kbd>函数一次替代多个值。
• 单元格中使用的公式<kbd>B1</kbd>
=REDUCE(A1:A10,{"[","'","(",")","]"," "},LAMBDA(x,y,SUBSTITUTE(x,y,)))
该解决方案基于以下问题和回答：由**JvdV先生提供的解决方案。参考帖子链接：复杂的替代，超出64个嵌套限制**
另一种替代方法是使用<kbd>TEXTSPLIT( )</kbd>函数。
• 单元格中使用的公式<kbd>B1</kbd>
=MAP(A1:A10,LAMBDA(x,CONCAT(TEXTSPLIT(x,{"[","'","(",")","]"," "},,1))))
英文:
Try using <kbd>REDUCE( )</kbd> function to substitute multiple values at once.
• Formula used in cell <kbd>B1</kbd>
=REDUCE(A1:A10,{"[","'","(",")","]"," "},LAMBDA(x,y,SUBSTITUTE(x,y,)))
The solution is based on the question and answer asked here: Solution provided by JvdV Sir. Reference link to post: Complex Substitute Beyond 64 Nesting Limit
Another alternative using <kbd>TEXTSPLIT( )</kbd>
• Formula used in cell <kbd>B1</kbd>
=MAP(A1:A10,LAMBDA(x,CONCAT(TEXTSPLIT(x,{"[","'","(",")","]"," "},,1))))
答案2
得分: 1
你可以使用：
B1 单元格中的公式：
=MAP(A1:A2,LAMBDA(s,CONCAT(TOCOL(--MID(s,ROW(1:17),1),3))))
英文:
You could use:
Formula in B1:
=MAP(A1:A2,LAMBDA(s,CONCAT(TOCOL(--MID(s,ROW(1:17),1),3))))
答案3
得分: 0
最简单的方法是使用Ctrl+H，仅替换（仅选择单元格）不必要的字符为空格。
如果您需要一个函数，可以使用SUBSTITUTE函数，并多次使用它来删除您想要替换的字符。
=SUBSTITUTE(SUBSTITUTE(A1,"(",""),"[","")
有关更多信息，请参阅函数文档。
英文:
The easiest option is to use Ctrl+H and replace (selection cells only) the unnecessary characters with blank space.
If you want a function, you can use SUBSTITUTE function and use that multiple times to remove the characters you want replaced
=SUBSTITUTE(SUBSTITUTE(A1,"(",""),"[","")
答案4
得分: 0
=VALUE(TEXTJOIN("",TRUE,IFERROR(VALUE(MID(A1,SEQUENCE(LEN(A1)),1)),"")))
MID将输入字符串拆分为单个字符。VALUE尝试将每个字符更改为数字。非数字字符将导致错误，因此IFERROR将其替换为空（""）。
然后，TEXTJOIN将所有部分重新组合在一起。它不使用分隔符，因此只有一个没有新内容的字符串。它忽略了IFERROR创建的所有空白字符。
诀窍是，现在你有一个字符串，这不适合使用自定义数字格式来以所需方式显示它们。但是...如果字符串以0开头，当你将文本结果转换为数字时，你将失去它（或它们）。
但这可以克服，所以再加一个VALUE将其转换为数字。
你之所以能克服这个问题，是因为除非你有需要多种格式并且长度不同的数字，否则你只需在格式字符串中使用“0”多次以获得所需数量的数字。因此，一个"0000-0000-00"字符串会显示为"0665-8888-01"，即使实际的前导0丢失了。
英文:
=VALUE( TEXTJOIN("",TRUE, IFERROR( VALUE( MID(A1,SEQUENCE(LEN(A1)),1) ), "") ) )
The MID tears apart the input string into single characters. VALUE attempts to change each character into a number. Non-numerals will result in an error, so IFERROR changes those out for a blank ("").
TEXTJOIN then puts all the parts back together. It does not use a delimiter so that there is just a string with nothing new. It ignores all the blanks that IFERROR created.
Trick is, you now have a string and that won't be amenable to using a custom number format on to display them in some desired way. But... if the string starts with a 0, you will lose it (or them) if you convert the text result into a number.
But that can be overcome, so one more VALUE makes it a number.
The reason you can overcome it is that, unless you have numbers that need a variety of formats and that differ in length, you can just use "0" in the format string as many times as you need numerals. So a "0000-0000-00" string would give "0665-8888-01" fr display, even though the actual leading 0 was lost.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论