Forum Discussion
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.
| WreckID | Name | Nationality | Type | Propulsion | Cause Lost | Year° | Year† | Weight | Latitude | Decimal Latitude | Longitude | Decimal Longitude |
| 664 | ?40°35'XXXN˜074°00'XXXW | 40°35.336'N | 40.6766667 | 74°00.005'W | -74.00138889 |
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)
7 Replies
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.
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)
Hi,
Try this formula:
=SUBSTITUTE(TEXTAFTER(B2, "˜"), "'XXX", ".005'")
or maybe you need this formula:
=SUBSTITUTE(TEXTAFTER(B2, "˜"), "'XXXW", ".005'P")
Hope this helps.
IlirU
