Find and replace specific characters within a cell based on positi

[ccgsuper]

I am attempting to replace specific characters within a cell based on their
position, but have not come up with a workable solution.
I need to always replace only the last two characters to the right side of
the cell with a 10 character entry containing two letters and 8 digits. The
search is limited to one column. As long as there is only one instance of
the two digits being replaced within a cell, all is fine, but if the cell
contains more than 1 instance of the two digits, both instances are replaced.

Example, assuming I use the replace command specifying I find "53" and
replace with "31" (assuming there is only one instance of 53 in a cell):

--Before--
AZ01003053
AZ01003153
AZ06100953
AZ01003253
AZ01006053

--After--
AZ01003031 (Correct)
AZ01003131 (Correct)
AZ06100931 (Correct)
AZ01003231 (Correct)
AZ01006031 (Correct)

Example, assuming I use the replace command specifying I find "53" and
replace with "31" (assuming there is more than one instance of 53 in a cell):

--Before--
AZ01003053
AZ01003153
AZ06100953
AZ01003253
AZ01006053
AZ19995353

--After--
AZ01003031 (Correct)
AZ01003131 (Correct)
AZ06100931 (Correct)
AZ01003231 (Correct)
AZ01006031 (Correct)
AZ19993131 (Incorrect)

Any help would be greatly appreciated.

ccgsuper

 

[Gord Dibben]

Use a helper column instead

In B1 enter =LEFT(A1,8) & "31"

Copy down to B6

When happy, copy B1:B6 and Paste Special>Values>OK>Esc to A1

Delete column B


Gord Dibben MS Excel MVP

 

[ccgsuper]

Does the trick, Thanks...

Use a helper column instead

In B1 enter =LEFT(A1,8) & "31"

Copy down to B6

When happy, copy B1:B6 and Paste Special>Values>OK>Esc to A1

Delete column B


Gord Dibben MS Excel MVP