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!

Nhập liệu Xử lí dữ liệu Excel Python Kiến trúc phần mềm

ID dự án: #38016104

Về dự án