CODEWITHSUNDEEP
excel
ninu
ninu

Reputation: 5

Excel Find & Replace Formula for Switching substrings

I want to swich one character to another. For example: interchanging all 3s to 5s and all 5s to 3s how can i achieve this with find and replace methods, or any other solution?

Upvotes: 0

Views: 53

Answers (2)

Error 1004
Error 1004

Reputation: 8220

I think you can use:

=IF(IF(ISERROR(FIND("3s",A1,1))=TRUE,0,1)=1,SUBSTITUTE(A1,"3s","5s"),IF(IF(ISERROR(FIND("5s",A1,1))=TRUE,0,1)=1,SUBSTITUTE(A1,"5s","3s")))

Upvotes: 0

Greg_D
Greg_D

Reputation: 150

My thought would be that you'd need to use nested SUBSTITUTE functions. If you did a straight 5 to 3 and 3 to 5, everything would end up as a 5. The first SUBSTITUTE would need to change the 5 to a character that woudn't show up anywhere. For example below I used a pipe delimiter as the intermediate place holder:

A1 : 355783

B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,5,"|"),3,5),"|",3)

Result: 533785

Upvotes: 2

Related Questions