Excel公式从字符串中删除一个字符，然后替换另一个与删除的字符相同的字符 [英] Excel formula to delete a character from a string then replace another character with the same character that was deleted

问题描述

我有一些坐标，希望删除一个。分隔符，然后用标准英国格式替换，十进制。字符。

例如9.811.365,054需要是9811365.054

我最初使用嵌套替代公式，但这也删除了。只剩下数字，即9811365054

解决方案

在立即窗口的VBE中 ctrl + g 尝试

 ？替换（replace（9.811.365,054，。，），，，。）
 9811365.054 
  

所以你只需要嵌套两个函数;首先用（ nothing ）替换所有的点，然后用点替换逗号。

，等效的公式将为 = SUBSTITUTE（SUBSTITUTE（A1，。，），，，。）假设 A1 是 9.811.365,054

I have a number of coordinates and wish to remove a "." separator, then replace a "," decimal with the standard UK format "." character.

e.g. 9.811.365,054 needs to be 9811365.054

I initially used a nested substitute formula but this also removed the "." leaving only numbers i.e. 9811365054

解决方案

In VBE in the Immediate Window ctrl+g try

? replace(replace("9.811.365,054",".",""),",",".")
9811365.054

So pretty much you just nest two functions; first you replace all the dots with "" (nothing) and then replace the comma with a dot.

and an equivalent formula would be =SUBSTITUTE(SUBSTITUTE(A1,".",""),",",".") assuming A1 is 9.811.365,054

这篇关于Excel公式从字符串中删除一个字符，然后替换另一个与删除的字符相同的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！