Create Macro to Standardize Naming Conventions

[david0985]

Hi,

I am not new to Excel, but I am very new to VBA.

I am looking to create a universal function/macro that takes various naming conventions and standardizes them. For example, depending on what report I am looking at, Queens, NY could be labeled as "QNS", "QU", or "Q". This is very bothersome because I frequently use the index/match function combination, and because of these different naming conventions, the index/match will fail. I am looking to create a function that will say, any time it sees either "QNS", "QU", or "Q", "QNS" will be spit out. Of course, I want this same formula to do a similar thing for other cities. I could make one giant if statement, but I really would prefer a nice clean custom formula that can be applied to all of my worksheets in the future.

Could you please help me with the frame work of the code and then I will insert the specific text? I believe this is a very simple request, but I have never used VBA code before.

Thanks in advance. Please let me know if you would like any additional details.

David

[6StringJazzer]

One approach to do this is to create a worksheet and in column A list all of the possible abbreviations that you want to use as standard. Then on each row, list all the variants that are acceptable. There is really no reasonable algorithm you can use to do this; you just have to list everything. Once you have that, writing the code is very straightforward. I have attached an example. The function is called STDABBREV and if given an abbreviation, will return the standard one.

You used the word "universal." If this is for general use, not just one workbook, then we might have to take a different approach and convert it to an add-in, which I think means that using a worksheet to map this won't work. But let's start here and see if it's what you need.

[david0985]

Thanks for the feedback Jeff.

I would like to use this custom function across all of my spreadsheets. However, if I just paste the code you wrote along with move and copying the "Key" tab you created into a new workbook where I wanted to use this function, it should work, correct?

I have read online about creating a module in order to build a custom universal function. This was my thought process (I probably have the syntax terribly wrong but I think you will get the idea). The actual code would have a lot more ElseIf statements:

Please Login or Register  to view this content.

Thanks for the help,

David

[6StringJazzer]

Yes, that's correct.

You could write a function like what you show, and it would certainly work just as well as mine. The only disadvantage is the amount of work required if you have to add/change an abbreviation. With my version you can generate a whole new set of data without changing any code. However, the code-only solution is more portable.

Here is the correct way to write your suggestion:

Please Login or Register  to view this content.

[david0985]

Works perfectly. Thanks for your clear and concise feedback!

[6StringJazzer]

You're welcome! Thanks for the rep and thanks for marking your thread Solved!