MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to delete/insert text into formulas

Posted by Barry Lindfield on April 02, 2001 9:10 AM

I've inherited a spreadsheet that contains a large number of links which I need to modify.

Could anyone advise the VBA code needed to delete the last ten characters of a formula and insert 10 new characters of text. i.e delete Frog311200 and replace with Frog311201.

Any help would be most appreciated

Posted by Dave Hawley on April 02, 2001 11:27 AM

Hi Barry

Could you simply use the Edit>Links|Change source ?

DaveOzGrid Business Applications

Posted by Barrie Davidson on April 02, 2001 3:01 PM

You could try selecting the entire spreadsheet and then performing a search and replace. In case you don't know how to do that, here's how. Ctrl+F will give you the "Find" pop-up box. Click on the replace button and then enter what you are looking for and what you want to replace that with.

The only bad thing about this is it might take a while to execute.

If you have any problems just drop me an e-mail and I'll try to help.

Posted by Barry Lindfield on April 04, 2001 3:05 AM

Thank Barrie/Dave your ideas worked well.