Find and replace specific characters within a cell based on positi
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 |
Find and replace specific characters within a cell based on positi
Use a helper column instead
In B1 enter =LEFT(A1,8) & "31" Copy down to B6 When happy, copy B1:B6 and Paste SpecialValuesOKEsc to A1 Delete column B Gord Dibben MS Excel MVP On Mon, 19 Jan 2009 15:29:01 -0800, ccgsuper wrote: 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 |
Find and replace specific characters within a cell based on po
Does the trick, Thanks...
"Gord Dibben" wrote: Use a helper column instead In B1 enter =LEFT(A1,8) & "31" Copy down to B6 When happy, copy B1:B6 and Paste SpecialValuesOKEsc to A1 Delete column B Gord Dibben MS Excel MVP On Mon, 19 Jan 2009 15:29:01 -0800, ccgsuper wrote: 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 |
All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com