Forum Discussion

Cygnata's avatar
Cygnata
Copper Contributor
Nov 20, 2025
Solved

Need to replace part of one cell with part of another

I need to take the XXX at the end of the name and replace it with the 005 at the end of the Longitude field. Is there any way to do this except manually? There's over 2000 to fix. Thanks. WreckI...
excel
  • m_tarler's avatar
    m_tarler
    Nov 20, 2025

    It shouldn't and didn't in my example I showed.  The 'inner' Substitution will only replace the first instance  of "XXX" with the value from J2 and the 'outer' Substitution will then replace the remaining "XXX" with L2.  If you only want to replace the 2nd instance then try this:

    =SUBSTITUTE(B2,"XXX",LEFT(TEXTAFTER(L2,"."),3),2)

     

Cygnata's avatar
Cygnata
Copper Contributor
Nov 20, 2025

The replacement text will never be the same, however.

IlirU's avatar
IlirU
Iron Contributor
Nov 21, 2025

 

Ok then, you can use this formula:

 

=REGEXREPLACE(TRIMRANGE(B2:B2500), "XXX", "005", 2)

 

Change the range as per you need.

 

Hope this helps.

IlirU