I am messing about here, and I am exporting my data as a fixed-width txt file. This means that I say “The first column starts at 1 and is 5 wide, the next one starts at 6 and is 3 wide” etc.
Thing is, in some of my columns, I have had to say they are 3 wide (cos some of the fields are 3 digits long), but a lot of them are only 2 digits long, so I don’t get a nice “tight” file, I get commas and crap. What I really want to do is “zerofill” those fields up to the right size. So if it is 15 in a 3 wide column, it will put a 0 in front of it, 015. At present I am doing this manually, formatting the rows as text in excel and then doing it by hand. Not much fun
Surely there is something in either excel or access that will do this for me?
I make no claims about the elegance of this code, but it works; I wrote it because I had to format a fixed-width text file with a variety of right and left justified values (padded with spaces), other values padded with leading zeroes and some text values for which the start or end could be safely truncated - it seemed to make sense to do it all in a multi-purpose function.
Public Function padtrim(mystr As String, length As Integer, rightJ As Boolean, padchar As String)
mystr = Trim(mystr)
padtrim = mystr
If Len(mystr) > length Then 'if it is too LONG
If rightJ = True Then 'and if we are justifying RIGHT
padtrim = right(mystr, length) 'cut characters off the left side
Else 'but if we are justifying left
padtrim = Left(mystr, length) 'cut characters off the right side
End If
End If
If Len(mystr) < length Then 'if it is too SHORT
If rightJ = True Then 'and if we are justifying RIGHT
padtrim = String(length - Len(mystr), padchar) & mystr 'pad out the left side
Else 'but if we are justifying left
padtrim = mystr & String(length - Len(mystr), padchar) 'pad out the right side
End If
End If
End Function
It’s a bit of a hack, but I usually use something like:
lPadLength = 3
lPadNumber = 12
If Len(CStr(lPadNumber)) < lPadLength Then
Right(String(lPadLength,"0") & CStr(lPadNumber),lPadLength)
Else
sPadOut = CStr(lPadNumber)
End If
The variable sPadOut will contain the zero-padded number. Numbers longer than the pad length will be returned intact.
Simple way to do it is to select the cells containing the data in Excel. Right click and go to format cells. Under the number tab click ‘Custom’ in the ‘Category:’ box then type ‘000’ (for three digits, more zeros for more digits) in the ‘Type:’ box. Click ok.
Any numbers that you subsequently type in these cells will be padded with the appropriate number of zeros.
This will only work for digits 0-9 not for other alpha-numeric characters.
(Tested on Excel 2000)
Hmm… imported it into access to make the fixedfile, and the bloody thing took away my 00’s! Can I make it a fixed file from excel? It keeps wanting to make it tabbspaced…
Or something altogether different?
If you want one of the ones above save it as comma delimited (.csv) and then just rename it to a .txt file. Then open it using notepad or textpad and do a find and replace, replacing the comma character with a space or with nothing.
Ah, I did a “Replace all” with nothing in the first box and 0 in the second to put zeros in all the empty fields (hope to hell that is all it did!) and will now re-do the format/Custom trick to make it be the right amount of zeros I will then save it as a csv, and then do a replace , with nada
