Jak
Well-known Member
- Joined
- Apr 5, 2002
- Messages
- 833
I use Excel to edit and arrange data into fields that I import to an Access Database. 70% of the time the data is non problematic. On those occasions when it is it takes a bit of time to delete unwanted formatting etc. One such problem is when I receive CSV files. Sometimes there are spaces after the text portions. This means that when the file is imported as comma delimited and a comma separates each field, there are still unwanted spaces after each text portion. I began by highlighting all the data and replacing the spaces with nothing. This was fine until I noticed that names that were originally separated by a space were now joined and had to have the spaces put back in. Small file sizes are quick to edit but larger files take an age.
As an example:
John James,123,Andrew Spencer,Y,123456,A,M
The above is an example of what each field might contain. There are no spaces after each text portion. Alternatively, here is a problematic example:
John James ,123 ,Andrew Spencer ,Y ,123456 ,A ,M
This is caused because the original data has spaces after each text portion. By using replace all spaces with nothing I end up with this:
JohnJames,123,AndrewSpencer,Y,123456,A,M
There is now no space between John and James or Andrew and Spenser. What I would like is a method of replacing the unwanted spaces without joining separate texts together. Any help in this capacity would be welcomed.
This message was edited by Jak on 2002-05-15 06:29
As an example:
John James,123,Andrew Spencer,Y,123456,A,M
The above is an example of what each field might contain. There are no spaces after each text portion. Alternatively, here is a problematic example:
John James ,123 ,Andrew Spencer ,Y ,123456 ,A ,M
This is caused because the original data has spaces after each text portion. By using replace all spaces with nothing I end up with this:
JohnJames,123,AndrewSpencer,Y,123456,A,M
There is now no space between John and James or Andrew and Spenser. What I would like is a method of replacing the unwanted spaces without joining separate texts together. Any help in this capacity would be welcomed.
This message was edited by Jak on 2002-05-15 06:29