Find and replace

Question

If this has been covered before I apologize in advance - I have looked.  I need to do a simple FIND AND REPLACE.  I need to change the abbreviation in a street address to a whole word, i.e., ST to STREET.  I have tried to do this but have found a problem - changing STATE ST I get **STREETATE STREET.**I am trying to help my daughter go through 65,000 addresses.  Any help would be appreciated.  I may need to use macros but I have no experience using them.  Thanks in advance

Answer 1

Assuming that all the street names are in a column, you can use a helper column that contains a cell formula that performs the substitution.

You'll have to think through the logic, but you can use a formula that looks at the 3 leftmost characters in the address for a space and the letters st and replaces them with the word Street.

You should investigate these formulas in Excel Help:

RIGHT

MID

FIND

LEN

CONCATENATE

Here's a video tutorial about Concatenate. Search the ExcelIsFun YouTube channel for more tutorials.

Excel Magic Trick 974: Formula to Join First & Last Name & Title: Ampersand or CONCATENATE?

Answer 2

The F&R engine in Mac Excel simply does not have the ability to do an Exact Match for partial cell content. If you have access to a recent PC version you may be able to do that type of replacement. Otherwise, in Mac Excel I believe it would require the use of VBA... Perhaps one of the coders around here will be able to provide the procedure you need.

Another option is to export the data in a Word-compatible file type, Word's F&R is capable of doing this type of Exact Match replacement. Then import the result back into Excel & resave using an Excel file type.

Answer 3

You are going to have do it in stages

First, try typing St for look up and Street for replacement be sure to leave on character blank space just after the T in ST to prevent the issue you are running into.