Search for data at the end of the string?

**Balthazar** · 06-18-2024, 08:33 AM

I am working on a long list of addresses. Almost all of the addresses have have the city at the end (like "1234 Main Street CITY"). I am wanting to remove the CITY from the address field for a vlookup that we do. There are two problems to this.
1. CITY isn't always fully spelled out (sometimes it's CI, or CIT)
and 2. Some of the street names contain the CITY word (so like 1234 CITY Street CITY) so a search and replace won't work.
The only thing they have in common is they are at the end of the text.

So... is there a way to search for data with nothing after it? I tried looking it up and the only thing I could find was the TEXTAFTER function, but that doesn't look like it will do what I need.
I am hoping there is a symbol I an use so I can do a find a replace for like CITY% and it will only remove the ones at the end.

Thank you.

**etaf** · 06-18-2024, 08:58 AM

What version of excel- you say 2016 - then a function that TEXTAFTER - not available in 2016 - so you must have a later version, please update profile , so we know what version we are providing a solution

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

**Balthazar** · 06-18-2024, 09:11 AM

Ok, I have updated my Office in my profile (it's been a while since I've been here). I also attached an example file with two columns. Column A is how the addresses are in my excel file and Column B is how I need them to look.
As you can see in column A, the city is "Buford" however, I can't just do a Find and Replace for that because there are roads named Buford as well. So I am wanting to know if it is possible to do a Find and Replace for "Buford" and tell it to only replace it if there is nothing after it (meaning it is at the end of the string).