Forum Discussion
Replace formula to remove multiple period signs (.) in a string
Hello
Our request is specific one which we need to use REPLACE or some other function (other than SUBSTITUTE) to replace period signs (.) in a string
example: 666.888.999 should be output as 666888999 once the formulae applied. We found a REPLACE formula can be used for 1 occurrence but not a multiple occurrence scenario. Following is what we did for one period (.) sign removal which is not enough when we have multiple.
find the index: =IF(ISNUMBER(SEARCH(".",A)),SEARCH(".",A),"0")
use the replace: =IF(_arg="0",A,REPLACE(A,_arg,1,""))
3 Replies
I would say these are not period signs in a string but thousand separators in a number.
And that can be dealt with by changing the number format.
Detlef_Lewin In the sample I put, it removed first period sign finding its index in the string (which is a number string yes) in the first formula and then execute the replace with "" in the second formula
I wanted a formulae to replace multiple of period signs (e.g: 666.777.888 has two period signs) as a requirement
IMPT: we cannnot use SUBSTITUTE formula
You are still talking about a string while I was suspecting it be a number.
So far you haven't proofed me wrong.
And SUBSTITUTE() is a valid Excel function! No reason not to use it.
