Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-30-2020, 04:55 AM
catflap's Avatar
catflap catflap is offline Leading zero in field Windows 7 64bit Leading zero in field Office 2013
Advanced Beginner
Leading zero in field
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default Leading zero in field

Hi


Importing a csv file into Excel where one of the fields has a leading zero is an old problem. Excel treats the field as a number and removes the zero.




There are several ways to fix this once you are in Excel - I favour putting in an apostrophe to force text instead of number format, for example - however, I'm trying to come up with a different solution.


I have a colleague who works with some mail merge software which can only accept csv files and works in the same way as Excel, ie dropping the leading zero. I don't have access to their software, but I figured if I could get Excel to accept it, then their software would work as well.


What I'm wondering is if there is any character I could put in at the start of the field which would force Excel to treat it as text, but which would not be visible when printed.


It could appear as a space. I've tried adding ASCII 255 for example, which is a non-breaking space, thinking if it wasn't the usual ASCII 32 space it might encourage Excel to treat it as text, but no luck.


Does anyone have any ideas on this one?


Thanks for reading!
Reply With Quote
  #2  
Old 01-30-2020, 07:23 AM
ArviLaanemets ArviLaanemets is offline Leading zero in field Windows 8 Leading zero in field Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Start Excel (no file opened). Start Open File dialog, set file type to text or csv file, and open the file. You are given the opportunity to define data types for columns - set for this column the data type Text.
Reply With Quote
  #3  
Old 01-30-2020, 09:27 AM
BobBridges's Avatar
BobBridges BobBridges is offline Leading zero in field Windows 7 64bit Leading zero in field Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Yeah, but that has to happen when his customer opens the workbook. The OP is looking for something he can put in that cell that'll handle the problem automatically, without the recipient having to do anything special.

I've never done this, but in at least the more recent versions of Excel I've seen an option that defines whether cell values in .csv files are store with quotes always, sometimes or never. I wonder whether setting that to Always (at your end) would result in the value "03" with quotes, and whether it would then read in as you want it at the other end?
Reply With Quote
  #4  
Old 01-30-2020, 09:30 AM
BobBridges's Avatar
BobBridges BobBridges is offline Leading zero in field Windows 7 64bit Leading zero in field Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

If you don't have that quote option available (or if it doesn't work as I expect), you might try modifying the text output, either in a VBA or by some other method, to ~force~ quotes around that column. Maybe it would still work at the other end.
Reply With Quote
  #5  
Old 02-04-2020, 07:32 AM
catflap's Avatar
catflap catflap is offline Leading zero in field Windows 7 64bit Leading zero in field Office 2013
Advanced Beginner
Leading zero in field
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

Hi


Thanks for responses - sorry for my late reply.


I was indeed hoping to add something to the text file to force it to work. Adding quotes just to the code field does nothing.


I thought I'd cracked it when I found that adding a tab would actually work (it's a csv file remember) - the field came into Excel as text, with leading zero preserved.


However, while this worked for Excel, it's sod's law that the other program I was trying to get a solution for still brought the field in as a number, and stripped the zero.


So I offer this solution up to anyone who has this issue with excel, but I'll just have to keep looking for something else for my particular requirement.


Cheers!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
alt + xxxx does not work in Onenote. The leading two bytes are ignored. garyp OneNote 0 07-11-2018 07:10 AM
Leading zero in field How to remove trailing & leading spaces in a cell? LearnerExcel Excel Programming 8 02-04-2018 08:22 PM
Get rid of leading spaces before paragraphs fig000 Word 11 03-14-2016 01:59 PM
Numbering with fixed length having leading 0 pmahesha Word 2 04-15-2015 11:48 PM
Change the leading of a character style??? Char t Word 1 11-05-2011 01:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft