I'm importing data from Excel 2010 to Access 2010. The data is fine, except that for one field in particular, there are about 9-15 spaces at the end of the data (which is city names). This will become a problem, because I need to concatenate the city name with the country name at some point, and instead of "London, Englad" I will get "London , England"...certainly not what I want.
The data was dumped into Excel from an outside database by an outside company that I do not have a connection with, so I am left to do this on my own. I can do a find & replace on a double-space, but this will still result in an extra space at the end of some names, resulting in "London , England" for example.
I can't do a replace on a single space, since some cities have spaces ("Salt Lake City" for example). SO, my question is first, is this actually an Excel question (ideally, this is done prior to import, I know...), or is there a way to do this in Access (I didn't discover this issue until the data was already imported)? And what I ultimately am asking, how do I find all values with a space at the very end, but not in the middle of the value ("Salt Lake City " vs. "Salt Lake City")?
Thanks in advance for any help, I hope I was clear enough in what I'm trying to do here!
The data was dumped into Excel from an outside database by an outside company that I do not have a connection with, so I am left to do this on my own. I can do a find & replace on a double-space, but this will still result in an extra space at the end of some names, resulting in "London , England" for example.
I can't do a replace on a single space, since some cities have spaces ("Salt Lake City" for example). SO, my question is first, is this actually an Excel question (ideally, this is done prior to import, I know...), or is there a way to do this in Access (I didn't discover this issue until the data was already imported)? And what I ultimately am asking, how do I find all values with a space at the very end, but not in the middle of the value ("Salt Lake City " vs. "Salt Lake City")?
Thanks in advance for any help, I hope I was clear enough in what I'm trying to do here!
