DessertDiva
New Member
- Joined
- Dec 5, 2020
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
I found this video about how to import a .csv file into Excel without first having to change it to a .txt file (Prevent Scientific Notation on Import). This technique worked amazingly well for saving me time on changing the file extension and keeping my long tracking number from showing as scientific notation. My only problem with this tip is that when I import the data and tell it that the delimiters are Tab, Comma, and Space, then change the tracking number column to be Text instead of General, every single segment of data gets put into their own cell now - the tracking number, the first name, the last name, each word/number of their address, etc. So one line could now be 15 cells across and one line could be 20 cells across depending on how big someone's address is. This means that data for a shipment could now be in various different columns instead of everyone's names being in one column, their address in one column, the cost in one column, etc. My tracking number is no longer in scientific notation thanks to this import tip but the rest of the data I need from this file is messed up.
If I import this way and only use the delimiters of Tab and Comma, my data remains in their appropriately aligned columns but my long tracking number has 4 trailing spaces. I can manually go into every cell and delete the trailing spaces without the tracking number switching to scientific notation. That is horrifically tedious when there are dozens of tracking numbers to edit one by one. But I can't do a Ctrl-H looking for four spaces and replacing it with nothing because that changes the tracking number to scientific notation. I can't do =TRIM for the same reason.
How can I import a .csv file this way but remove just the trailing spaces on the tracking number leaving all other spaces intact? Or how can I remove trailing spaces from a very long number and not have it change to scientific notation?
If I import this way and only use the delimiters of Tab and Comma, my data remains in their appropriately aligned columns but my long tracking number has 4 trailing spaces. I can manually go into every cell and delete the trailing spaces without the tracking number switching to scientific notation. That is horrifically tedious when there are dozens of tracking numbers to edit one by one. But I can't do a Ctrl-H looking for four spaces and replacing it with nothing because that changes the tracking number to scientific notation. I can't do =TRIM for the same reason.
How can I import a .csv file this way but remove just the trailing spaces on the tracking number leaving all other spaces intact? Or how can I remove trailing spaces from a very long number and not have it change to scientific notation?