IF with two REPLACE statements to move character in string

Occasional Visitor



I have a 40 character-long string in which I want to check if there is a C on the 23rd position and if so, move that C to the 37th position. Right now, I'm trying to do it with the following formula.


=IF((MID(B8;23;1)="C");AND(REPLACE(B8;23;1;" ");REPLACE(B8;37;1;"C"));FALSE)


This is unfortunately giving me #VALUE errors. 


Am I doing something wrong? And is there perhaps an easier way to resolve my use case?

3 Replies

Hi @kyrillkaz 


You may try below formula:



A sample file is also attached for your reference. Hope it will work as desired.







As variant

   str, B8, n, 23, m, 37,
   k, SEQUENCE(LEN(str)),
   IF( (k>=n)*(k<m), MID(str,k+1,1),
   IF(k=m, "C", MID(str,k,1))))