Hi Excel'rs
I have created a word list of about 40 or so words that I want to do a find and replace with their abbrievations. Now, I know I can go and record a macro and just open the find/replace option in excel and manually do each word. However, I knwo this list is only going to grow with time. Is there a way to name each column of words a range and build a macro that goes line by line... finding for example the word debit and replacing it with dr?/
Thanks
JP
Last edited by CharterJP; 12-01-2010 at 04:36 PM.
Hi,
One way,
Put your 40 words in a list with their abbrevations alongside in the column to the right and name the list of words (just the range containing the words, not the two columns) 'word_list'. It's a good idea to make this a dynamic range name so that you can add words as necessary.
Now with your cursor in the column of words you want to change run the following macro..
HTH
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
My version:
Mass Replace
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you guys,
Richard, your solution would not work simplely because the tab containing the words to be replaced is a tab that will be uploaded into a system daily and has to be the same formate always. The problem I am having is that the description column for that tab contains more than 30 characters and the system it is going into can only have 30 max for a discription. I've used the Trim function to get rid of excess spaces, but need to go shorter. And the discriptions are important enough to were I can just remove the last group of characters to make fit. So I need to find words like Credit and replace with cr and Debit and replace with dr... etc.
Hope this helps
JP
My macro will do what you want, but be warned, make sure you order that list of find strings carefully. You don't want to inadvertently create a string that will later be replaced by another.
JBeaucaire,
I am looking at your link now, thank you for providing it. I will try to impletement correctly. If I have questions, can I contact you?
Absolutely. I check in frequently.
First question, in the sheets("categories") Is your range, both the words to be found and their replacement.Please Login or Register to view this content.
For mine, I have a sheet "wordlist" and Column A is the words to find and B is their replacement. Would this still work or do I need to reorganize?
The code makes a RNG out of the entries in column A, then loops through them one at at time. During the replacement process, it puts in whatever is in column B.
You can just edit the code to your sheetname.
I am getting a 400 runtime error... Any ideas as to what that means?
Ok, I got it to work and it did great. however, when I ran the macro... It got rid of all spaces. Any ideas
I got it to work... Thank you very much for the macro and the help.
Hi there, just for info. Such replacement can be done with one-line code:
with no cycle through the range.Please Login or Register to view this content.
Watersev, be sure to read the first post more closely. The OP is wanting to do multiple sequential find/replace commands as outlined throughout this thread.Originally Posted by watersev
Hi,
I see you've ended up with an answer so this is somewhat academic but I see no reason why the technique I described wouldn't have worked.If length of string was some sort of issue then a '*' in the Find would presumably have worked.
Rgds
sorry, I have not noticed that clause
I have tried your code JB, but it comes up with a 400 error? I am new to VB and coding, so have absolutely no idea what is going wrong.
Ive attached what Ive done
The file you uploaded has no macro code in it, so I can't see what you fixed since your layout is different from the sample file on the code site. My sample workbook has the values being replaced in column B. Yours has them in column A.
Look at the code and change the Column B reference to column A before you try that macro, or move the data into column B. One or the other.
I need to find a predefined list of values in worksheet A in worksheet B and remove them from worksheet B. I believe this macro would work perfectly for me. Can you post this macro in an excel workbook?
The post #3 in this thread includes a link to the macro and a sample workbook file.
Thanks for the quick response. I couldn't find the sample workbook file. Is there a way to re-post it?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks