Import .csv file and remove trailing spaces

DessertDiva

New Member
Joined
Dec 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. 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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows
I think it would be most beneficial to us if you could post a sample of what your data looks like, and then show us how you want that sample to look after importing into Excel.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

DessertDiva

New Member
Joined
Dec 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Here's the condensed .txt file (originally contained nearly 50 lines of data) - this is the .csv file saved as a .txt file and then opened in Excel, choosing Delimited, choosing Tab and Space as the delimiters, changing the Order No. column to Text, then clicking Finish. Once imported, there are now 4 trailing spaces behind the Order No. and I'm seeking a quick way to get rid of them. As shared before, I cannot do a Ctrl-H looking for 4 spaces and replacing with nothing because it changes the Order No. to scientific notation. I can't do a TRIM function either for the same reason. Right now, the only thing that works is manually deleting the 4 spaces in every single cell in that column.

I don't know how you'll be able to help me because when I preview this mini-sheet, the four trailing spaces don't exist and when I cut-n-paste this mini-sheet into a blank Excel sheet, the Order No. is converted to scientific notation with no trailing spaces.

Mr. Excel 2021.06 DAT 421967 CA.txt
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Wlr Batch No.Shipper's CodeOrder No.Ship DateCustomerAddressCitySku No.HS CodeCtryQtyPriceLine TotalExchange RateCAD ValueDuty RateTax Rate GSTTax Rate HSTTax Rate PSTTotal DutyTotal GSTTotal HSTTotal PSTTotal SIMATotalDescriptionWLR Transaction No.Release DateDuty (USD)VAT (USD)SIMA (USD)Deferment FeeDeferment TotalTotal (USD)
24067APC23851814102260001404########STEPHEN ZHOU36 WATERSTONE STRICHMOND HILLSKU_R_26195204820.10.00.00US10.640.641.20610.7700130000.1000.1NOTECARD13201 268935303########00.0800.0500.08
34067APC23851814102260001407########RUI WANG26 MOWER AVEVAUGHANSKU_R_26195038518.21.00.00US146.9946.991.206156.6700130007.37007.37SPEAKER13201 268935278########06.1100.050.316.42
44067APC23851814102260001416########SIVA SAMBASIVAM115 GEDDINGTON CRESMARKHAMSKU_R_26195144820.10.00.00US10.640.641.20610.7700130000.1000.1NOTECARD13201 268935290########00.0800.0500.08
54067APC23851814102260001421########WEI ZHOU56 FROBISHER STRICHMOND HILLSKU_R_26195278518.21.00.00US146.9946.991.206156.6700130007.37007.37SPEAKER13201 268935314########06.1100.050.316.42
64067APC23851814102260001428########LARRY JOHN312 ALBRIGHT RDBRAMPTONSKU_R_26194894820.10.00.00US10.640.641.20610.7700130000.1000.1NOTECARD13201 268935267########00.0800.0500.08
74067APC23851814102260001437########SHARMEELAN MAHENDRARAJAH1024 TIGERLILY TRAILPICKERINGSKU_R_26195098518.21.00.00US146.9946.991.206156.6700130007.37007.37SPEAKER13201 268935289########06.1100.050.316.42
Mr. Excel 2021.06 DAT 421967 CA
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows
Insert a blank column between C and D.

If the Order No. field is a numeric, try dividing by 10000, i.e.
=C2/10000

Or, if it is text with 4 zeroes at the end, use this formula on C2:
=LEFT(C2,LEN(C2)-4)

One of those two ways should work.
 

DessertDiva

New Member
Joined
Dec 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not trying to get rid of extra zeroes. I'm trying to get rid of 4 trailing spaces after the Order #.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,027
Office Version
  1. 365
Platform
  1. Windows
You have a number of options. Firstly do you want to finish up with a text value or a number.
Recap: The aim is to get "1814102260001400 " to "1814102260001400"

1) Trim will work - result will be text
The only issue that if you insert a column after column C and column C is formatted as text, the new column D will default to text as well. For Trim or any formula to work in Column D you need to set the format of Column D to General.

2) Highlight the column - result will be text
Text to columns > Delimited
Delimiter = Space, Treat consecutive delimiters as one = TRUE
On the next screen set column 1 to text and if you get a column 2 set it to skip
(if you had 2 columns its because of the space in Order No and you will need to fix the heading - it will have lost the No part of the heading)

3) Highlight the column - result will be number
Text to columns > Delimited
Delimiter = Space, Treat consecutive delimiters as one = TRUE
On the next screen set column 1 to General and if you get a column 2 set it to skip
(if you had 2 columns its because of the space in Order No and you will need to fix the heading - it will have lost the No part of the heading)
Yes this will give you Scientific Notation but just format the column as Custom with a format of #

4) Your replace of 4 spaces - result will be number
When you get Scientific Notation just format the column as Custom with a format of #

5) Select any blank cell and hit copy - result will be number
The paste special and select "Add"
When you get Scientific Notation just format the column as Custom with a format of #
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not trying to get rid of extra zeroes. I'm trying to get rid of 4 trailing spaces after the Order #.
If there are 4 trailing spaces after the number, then you probably have a text entry and my second suggestion should work. Did you try it?
It does not matter whether it is 4 zeroes or 4 spaces, that second solution removes the last 4 characters, regardless of what they are.
 

DessertDiva

New Member
Joined
Dec 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
@Alex Blakenburg - thank you! Your Option #2 did the trick. Much appreciated!

@Joe4 - thank you, as well. I went back and tested your Option #2 and it works, too, but it wasn't originally clear enough that it would take care of the last 4 characters no matter what they were.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,027
Office Version
  1. 365
Platform
  1. Windows
Thanks for letting us know. Glad you now have some options that work for you.
 

Forum statistics

Threads
1,143,909
Messages
5,721,450
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top