Import .csv file and remove trailing spaces

[DessertDiva]

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?

 

[Joe4]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Wlr Batch No.	Shipper's Code	Order No.	Ship Date	Customer	Address	City	Sku No.	HS Code	Ctry	Qty	Price	Line Total	Exchange Rate	CAD Value	Duty Rate	Tax Rate GST	Tax Rate HST	Tax Rate PST	Total Duty	Total GST	Total HST	Total PST	Total SIMA	Total	Description	WLR Transaction No.	Release Date	Duty (USD)	VAT (USD)	SIMA (USD)	Deferment Fee	Deferment Total	Total (USD)
2	4067	APC2385	1814102260001404	########	STEPHEN ZHOU	36 WATERSTONE ST	RICHMOND HILL	SKU_R_2619520	4820.10.00.00	US	1	0.64	0.64	1.2061	0.77	0	0	13	0	0	0	0.1	0	0	0.1	NOTECARD	13201 268935303	########	0	0.08	0	0.05	0	0.08
3	4067	APC2385	1814102260001407	########	RUI WANG	26 MOWER AVE	VAUGHAN	SKU_R_2619503	8518.21.00.00	US	1	46.99	46.99	1.2061	56.67	0	0	13	0	0	0	7.37	0	0	7.37	SPEAKER	13201 268935278	########	0	6.11	0	0.05	0.31	6.42
4	4067	APC2385	1814102260001416	########	SIVA SAMBASIVAM	115 GEDDINGTON CRES	MARKHAM	SKU_R_2619514	4820.10.00.00	US	1	0.64	0.64	1.2061	0.77	0	0	13	0	0	0	0.1	0	0	0.1	NOTECARD	13201 268935290	########	0	0.08	0	0.05	0	0.08
5	4067	APC2385	1814102260001421	########	WEI ZHOU	56 FROBISHER ST	RICHMOND HILL	SKU_R_2619527	8518.21.00.00	US	1	46.99	46.99	1.2061	56.67	0	0	13	0	0	0	7.37	0	0	7.37	SPEAKER	13201 268935314	########	0	6.11	0	0.05	0.31	6.42
6	4067	APC2385	1814102260001428	########	LARRY JOHN	312 ALBRIGHT RD	BRAMPTON	SKU_R_2619489	4820.10.00.00	US	1	0.64	0.64	1.2061	0.77	0	0	13	0	0	0	0.1	0	0	0.1	NOTECARD	13201 268935267	########	0	0.08	0	0.05	0	0.08
7	4067	APC2385	1814102260001437	########	SHARMEELAN MAHENDRARAJAH	1024 TIGERLILY TRAIL	PICKERING	SKU_R_2619509	8518.21.00.00	US	1	46.99	46.99	1.2061	56.67	0	0	13	0	0	0	7.37	0	0	7.37	SPEAKER	13201 268935289	########	0	6.11	0	0.05	0.31	6.42
Mr. Excel 2021.06 DAT 421967 CA

 

[Joe4]

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]

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]

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]

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]

@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]

Thanks for letting us know. Glad you now have some options that work for you.