Is there a way to automatically replace abbreviations with full meanings?

**thesuperguy** · 08-19-2014, 11:02 PM

I work with large columns of addresses, all notated in abbreviations such as AVE, and ST, etc. I am required to expand these abbreviations to their full name and I currently use the find and replace option to do this. However, the problem with the find and replace option is that quite often, what I'll find is that when I'm trying to replace something such as ST to Street, the find and replace function will find all other instances of ST where they are not meant to represent Street, and turn them into Street. Because of this, I would like to know if there is a way to assign individual abbreviations, and series of characters, a true expanded meaning, and to have excel automatically detect these instances and replace them?

**boopathiraja** · 08-19-2014, 11:16 PM

Some thing like this where A1 is the referring cell

Formula:

Please Login or Register  to view this content.

**gak67** · 08-19-2014, 11:17 PM

Using find and replace still, if you put " ST" (without the quotes - they were just put there to show the space before the S) it will only find those instances at the start of a word and have the replace string as " Street" (again without the quotes).

Likewise, " ST " and " Street ", " ST." and " Street.", and " ST," and " Street," can be used. It really depends on how your data is formatted. Making sure you have the Match Case box ticked might also help (click on the options button if you cant see it).

Any formula to do the replacement will have the same difficulty in finding the correct instances of "ST"

**thesuperguy** · 08-19-2014, 11:30 PM

Thanks guys for the suggestions.

@gak67

I was already doing that, but there are instances where the names of streets will also start with ST and it might turn into something humorously named like Streetaffer and such. It seems there is no 100% perfect way of doing this so I guess I'll just stick to find and replace function and single out the exceptions. Also, @boopathiraja, I would use that formula, but I'm under the impression that it has the same restrictions as the find and replace function.

**gak67** · 08-19-2014, 11:33 PM

Is there a comma or period or space after the street name, eg "Staffer St, Erehwon"? Then you could use " St," as the find string and " Street," as the replace string.

**protonLeah** · 08-19-2014, 11:55 PM

Deleted ...

**sktneer** · 08-19-2014, 11:57 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**thesuperguy** · 08-20-2014, 12:17 AM

Here's the example. Note the last example with the street name starting with ST.

**sktneer** · 08-20-2014, 12:29 AM

May be this...

Assuming that your address is in A1, then

Please Login or Register  to view this content.

Does this help?

**sktneer** · 08-20-2014, 12:32 AM

Or more correctly this.....

Please Login or Register  to view this content.

**gak67** · 08-20-2014, 12:42 AM

Using your sample data and a Find and Replace, with " ST" as the find string, ie with a space before the S, and " Street" as the replace string (again with a space before the S), it did not affect the ST at the start of "STAFFORD".

**thesuperguy** · 08-20-2014, 12:47 AM

@gak67

The reason it didn't affect STAFFORD is because I removed the house numbers on the addresses. There would normally also be a space before STAFFORD thus causing a few problems.

@sktneer

Your formula seems to be working quite well so far. I'll report back if anything else comes up.

**gak67** · 08-20-2014, 01:05 AM

So can you upload some sample data that reflects real life? Include everything that is in the cell, both before and after the bit you have shown. Create dummy addresses if necessary to ensure confidentiality.

**thesuperguy** · 08-20-2014, 01:22 AM

Here's some less than realistic addresses that are formatted accordingly.

**kvsrinivasamurthy** · 08-20-2014, 03:01 AM

Pl see attached file with macro, which replaces the required data.

**sktneer** · 08-20-2014, 03:14 AM

Formula for the updated sheet...

Please Login or Register  to view this content.

Is there a way to automatically replace abbreviations with full meanings?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Re: Is there a way to automatically replace abbreviations with full meanings?

Thread Information

Users Browsing this Thread

Similar Threads

VBA help needed to replace titles with abbreviations

Excel 2007 : Mass find and replace - changing abbreviations to full words

Abbreviations to full Words

entering data as abbreviations and outputting full words

Replace a long list of abbreviations with full words in Excel

Bookmarks

Bookmarks

Posting Permissions