Maco that will remove certain characters

Ok I have to manually go through about 9,000 workbooks. In cell E43 of a certain sheet called "list" I have to delete underscores(_) and replace them with a single space. and remove the Rev** after each name

In example: company_name_t45671000_RevA2

Will look like this when I'm done: company name t45671000

Now I've tried to make a Macro that will delete the underscores and the Rev which worked fine except that it replaced the names with the the name that the macro was recored under.

IE: The first sheet I done worked fine when I hit the keyboard shortcut command which was company_name_t45671000_RevA2.

The second workbook sheet of "list" got fixed but had the name of the one I fixed before it: company name t45671000, where it should have been "company name s6743245.

Is there a way around this?

Also sometimes the sheets are protected, is there a way to incorporate "unprotect sheet" when it needs to be unprotected and then after the file has been corrected, re-enable protection again?

Thanks, Sean.

It would be helpful to see your code.

For the protection, try adding a command button with this code:
Private Sub CommandButton1_Click()
With Sheets("Sheet1")
.Unprotect
.Range("A1") = "Hello 2 u" '(or do stuff here)
.Protect
End With
End Sub

floydbird, please edit your post to add code tags.

Seanstex, the forum's requirement that code have code tags applies equally to you. Please edit your post to add them.

If there isn't a way for a Macro to fix my problem, Find and Replace will work; however, is there a way that I can find and replace more than one thing at a time?

I can do "Find:"_
Replace:space

Can I do that and say...Rev1B or whatever at the sametime?

I tried:

Find:_, Rev** and "_", "Rev**"

didn't work lol

I'd like to replace Rev** with spaces

Originally Posted by shg

Try this:

Please Login or Register  to view this content.

Ah! that works perfectly

Except, I forgot to mention that some of the file-names end in ".lab" without the quotes and the .text conflicts with that by deleting everything before .lab!

Like this: Company Name t64567421K.lab

after running Macro... looks like this: Company Name

Is there a way around that?

Originally Posted by shg

Please Login or Register  to view this content.

Wow works great...

lol

except with this name: Delta_Montrose_iS02A99002.Lab it doesn't delete the .lab but does delete the underscores.

One last request on some of the files such as:Coos-Curry it deletes everything but the hyphen.


Honestly, I want to send some money your way if you have Paypal. You just saved me about a weeks worth of work.

Thanks so much for the help!

... on some of the files such as Coos-Curry it deletes everything but the hyphen

Don't think so. Check your code and try again.

Delta_Montrose_iS02A99002.Lab it doesn't delete the .lab

Look at Help for the InStrRev function and modify the arguments to do a case-insensitive compare.