Forum Discussion
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...
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)
That replaces BOTH XXXs with the same value.
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)
That worked, thank you!