Apr 07 2021 01:55 AM
Hi,
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?
Apr 07 2021 02:14 AM
Hi @kyrillkaz
You may try below formula:
=IF(MID(B8,23,1)="C",LEFT(B8,22)&MID(B8,24,14)&"C"&RIGHT(B8,3),B8)
A sample file is also attached for your reference. Hope it will work as desired.
Thanks
Tauqeer
Apr 07 2021 02:30 AM
=IF(MID(B8,23,1)="C",REPLACE(REPLACE(B8,23,1,""),37,0,"C"),B8)
Apr 07 2021 05:08 AM
As variant
=TEXTJOIN("",1,
LET(
str, B8, n, 23, m, 37,
k, SEQUENCE(LEN(str)),
IF(MID(str,n,1)="C",
IF( (k>=n)*(k<m), MID(str,k+1,1),
IF(k=m, "C", MID(str,k,1))))
))