Forum Discussion

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

m_tarler

Bronze Contributor

Nov 20, 2025

I suggest make a new column for the updated Name and you can use:

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

I know you only asked for the Longitude one but just in case you needed both I did both figuring you could delete the part you don't need. If you really don't want that Name2 column then you can do it, copy the column and then Paste Values Only overtop the Column B values.

Cygnata
Copper Contributor
Nov 20, 2025
That replaces BOTH XXXs with the same value.
- m_tarler
  Bronze Contributor
  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
    Copper Contributor
    Nov 20, 2025
    That worked, thank you!