I have a column in Excel that has a lot of mac addresses, and they were copied like this:
我在Excel中有一个包含大量mac地址的列,它们被复制如下:
0080.9fc9.a2ff
I wanted to remove the dots in it so I could copy and paste it to another place without having to do it mac by mac.
我想删除它中的点,这样我就可以将它复制并粘贴到另一个地方,而无需通过mac进行mac。
I tried this formula
我试过这个公式
=SUBSTITUTE(C1;".";"")
It used to give me a circular reference error but I enabled iterative calculations and now it returns me a 0 instead of (following the first example)
它曾经给我一个循环引用错误,但我启用了迭代计算,现在它返回0而不是(在第一个例子之后)
00809fc9a2ff
As I wanted it to.
正如我所希望的那样。
Edit: To help pinning down this issue I'm screenshotting exactly what is happening.
编辑:为了帮助确定这个问题,我正在截断发生了什么。
2 个解决方案
#1
I was able to reproduce your circular reference error. I just had to type your formula in the C1 cell!
我能够重现你的循环引用错误。我只需要在C1单元格中输入你的公式!
=SUBSTITUTE(C1;".";"")
I think you just got the coordinates wrong.
我觉得你的坐标错了。
To make it clear: you can't replace cell contents on the fly using a formula. You need to create a new cell to store the results. Otherwise, you'll need to use good old search and replace dialogue.
为清楚起见:您不能使用公式动态替换单元格内容。您需要创建一个新单元格来存储结果。否则,您将需要使用良好的旧搜索并替换对话。
#2
You can try this:
你可以试试这个:
=substitute(C1,".","")
ie, instead of semicolon ; try to use comma ,
即,而不是分号;尝试使用逗号,
#1
I was able to reproduce your circular reference error. I just had to type your formula in the C1 cell!
我能够重现你的循环引用错误。我只需要在C1单元格中输入你的公式!
=SUBSTITUTE(C1;".";"")
I think you just got the coordinates wrong.
我觉得你的坐标错了。
To make it clear: you can't replace cell contents on the fly using a formula. You need to create a new cell to store the results. Otherwise, you'll need to use good old search and replace dialogue.
为清楚起见:您不能使用公式动态替换单元格内容。您需要创建一个新单元格来存储结果。否则,您将需要使用良好的旧搜索并替换对话。
#2
You can try this:
你可以试试这个:
=substitute(C1,".","")
ie, instead of semicolon ; try to use comma ,
即,而不是分号;尝试使用逗号,