Hi,
I have a column that has cities. One row has the street address. I want to keep the street address so I can merge it with the Street Address Column. I have done this by replacing the street address with a number then changing the type of the column to a number, replacing the errors with null, changing the column back to text, then replacing the number with the street address. Phew... it worked. But is there an easier method?
Hi Angela,
You lost me at "One row has the street address.", what do the other rows have? Can you please share some sample data so we can see your question in context.
Thanks,
Mynda
Sorry, I will definitely have to work on my explanations. I have a spreadsheet file with columns for Name, Address, City, Province, Postal Code. In one of the rows of data there is a street address in the City column. I would like to move this to the Address Column. I have attached the file for review. This file needs a lot of cleansing and I'm using it as a training exercise.
You will need to identify the things that will separate Cities from street addresses. Unfortunately, there is no easy way for that: lots of inconsistencies in your data. You can try setting a list of Cities, and add a formula in power query to check if the city is in the list of cities. But the City column has lots of problems and text errors, commas, spaces, that will fail to identify all possible variation.
The safest way is manual evaluation I'm afraid, otherwise you might move cities into the Address column, which is not a desired situation.
