IF with two REPLACE statements to move character in string

%3CLINGO-SUB%20id%3D%22lingo-sub-2258454%22%20slang%3D%22en-US%22%3EIF%20with%20two%20REPLACE%20statements%20to%20move%20character%20in%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258454%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%2040%20character-long%20string%20in%20which%20I%20want%20to%20check%20if%20there%20is%20a%20C%20on%20the%2023rd%20position%20and%20if%20so%2C%20move%20that%20C%20to%20the%2037th%20position.%20Right%20now%2C%20I'm%20trying%20to%20do%20it%20with%20the%20following%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((MID(B8%3B23%3B1)%3D%22C%22)%3BAND(REPLACE(B8%3B23%3B1%3B%22%20%22)%3BREPLACE(B8%3B37%3B1%3B%22C%22))%3BFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20unfortunately%20giving%20me%20%23VALUE%20errors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20doing%20something%20wrong%3F%20And%20is%20there%20perhaps%20an%20easier%20way%20to%20resolve%20my%20use%20case%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2258488%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20with%20two%20REPLACE%20statements%20to%20move%20character%20in%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258488%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1019113%22%20target%3D%22_blank%22%3E%40kyrillkaz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20below%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(MID(B8%2C23%2C1)%3D%22C%22%2CLEFT(B8%2C22)%26amp%3BMID(B8%2C24%2C14)%26amp%3B%22C%22%26amp%3BRIGHT(B8%2C3)%2CB8)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference.%20Hope%20it%20will%20work%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?

3 Replies

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

@kyrillkaz 

=IF(MID(B8,23,1)="C",REPLACE(REPLACE(B8,23,1,""),37,0,"C"),B8)

@kyrillkaz 

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))))
))