Automated Replace Function Creation

I need your help in creating an automated script or formula that can run multiple find-&-replace functions, at the same time, on an Excel spreadsheet that could regularly include 1,000 to 5,000+ records. Specifically, I will run this routine on the physical address column of data (not on the Company Name or City or State columns). In the attached .xlsx file, I would run the routine for all of a Column B.

Here are the 20 specific tasks involved:

1. If a cell contains the word "Ave" or "Ave.", it should be replaced with "Avenue".

2. If a cell contains the word "Blvd" or "Blvd.", it should be replaced with "Boulevard".

3. If a cell contains the word "Ct" or "Ct.", it should be replaced with "Court".

4. If a cell contains the word "Drive" or "Dr.", it should be replaced with "Drive".

5. If a cell contains the word "Expwy" or "Expwy." or "Epwy" or Epwy.", it should be replaced with "Expressway".

6. If a cell contains the word "Hwy" or "Hwy.", it should be replaced with "Highway".

7. If a cell contains the word "Ln" or "Ln.", it should be replaced with "Lane".

8. If a cell contains the word "Pkwy" or "Pkwy.", it should be replaced with "Parkway".

9. If a cell contains the word "Rd" or "Rd.", it should be replaced with "Road".

10. If a cell contains the word "St" or "St.", it should be replaced with "Street".

11. If a cell contains the word "1st", it should be replaced with "First".

12. If a cell contains the word "2nd", it should be replaced with "Second".

13. If a cell contains the word "3rd", it should be replaced with "Third".

14. If a cell contains the word "4th", it should be replaced with "Fourth".

15. If a cell contains the word "5th", it should be replaced with "Fifth".

16. If a cell contains the word "6th", it should be replaced with "Sixth".

17. If a cell contains the word "7th", it should be replaced with "Seventh".

18. If a cell contains the word "8th", it should be replaced with "Eighth".

19. If a cell contains the word "9th", it should be replaced with "Nineth".

20. If a cell contains the word "10th", it should be replaced with "Tenth".

NOTE 1: I do NOT want the routine to find & replace any of the abbreviations, like "St" in the Proper Name of the Ave/Blvd/Ct/etc.

For example, I would NOT want a "123 Strausburg St." address to turn into "123 Streetrausburg Street".

This note would equally apply to "Ave/Ave", "Blvd", "Ct", "Dr", "Expwy", "Epwy", "Hwy", "Pkwy", "Rd".

NOTE 2: I do NOT want the routine to find-&-replace:

"123 Strausburg St., S.W."/"123 Strausburg St., SW"/"123 Strausburg St. S.W."/"123 Strausburg St. SW",

with:

"123 Strausburg StreetS.W." or "123 Strausburg StreetSW"

In other words, I do not want to lose the appropriate "space/spacebar" between the "Street" and "S.W." (or "SW"). In my experience, it has been too easy to miss this pesky space/spacebar.

NOTE 3: I would expect to receive the routine to be given to me in such a way that I could paste into my spreadsheet.

NOTE 4: I would expect that I could add, in the future, additional "street"-like names into the routine, assuming it was intuitive, OR, I would need written-out instructions (if it was not intuitive).

Required skills and experience:

- Proficient in Microsoft Excel

- Experience in creating formulas or scripts for automated processes

- Attention to detail for accurate data replacement

I require this script or formula to be implemented as soon as possible. Looking forward to your applications!

Data Entry Data Processing Excel Python Software Architecture

Project ID: #38016104

About the project