# Again with the Excel/Access questions!

**URL:** <https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282>\
**Category:** Factual Questions\
**Created:** [November 20, 2003, 10:46am UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282 "2003-11-20T10:46:45Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Iteki](https://avatars.discourse-cdn.com/v4/letter/i/eada6e/32.png) [@Iteki](https://boards.straightdope.com/u/Iteki)\
**Post date:** [November 20, 2003, 10:46am UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/1 "2003-11-20T10:46:45Z")

</div>

I really should go a course…

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?

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 20, 2003, 11:15am UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/2 "2003-11-20T11:15:36Z")

</div>

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.

```auto

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

```

so…

Padtrim(“ABCDEF”,7,True,“0”) returns “0ABCDEF”  
Padtrim(“ABCDEF”,7,False,“0”) returns “ABCDEF0”  
Padtrim(“ABCDEF”,5,True,“0”) returns “BCDEF”  
Padtrim(“ABCDEF”,5,False,“0”) returns “ABCDE”

---

<div class="post-metadata">

**Author:** ![Armilla](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Armilla](https://boards.straightdope.com/u/Armilla)\
**Post date:** [November 20, 2003, 11:37am UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/3 "2003-11-20T11:37:15Z")

</div>

It’s a bit of a hack, but I usually use something like:

```auto

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.

---

<div class="post-metadata">

**Author:** ![Armilla](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Armilla](https://boards.straightdope.com/u/Armilla)\
**Post date:** [November 20, 2003, 11:38am UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/4 "2003-11-20T11:38:20Z")

</div>

D’oh. Serves me right for coding right into the reply box. It should read:

```auto

lPadLength = 3
lPadNumber = 12

If Len(CStr(lPadNumber)) < lPadLength Then
    sPadOut = Right(String(lPadLength,"0") & CStr(lPadNumber),lPadLength)
Else
    sPadOut = CStr(lPadNumber)
End If

```

---

<div class="post-metadata">

**Author:** ![fezpp](https://avatars.discourse-cdn.com/v4/letter/f/da6949/32.png) [@fezpp](https://boards.straightdope.com/u/fezpp)\
**Post date:** [November 20, 2003, 12:25pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/5 "2003-11-20T12:25:31Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Armilla](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Armilla](https://boards.straightdope.com/u/Armilla)\
**Post date:** [November 20, 2003, 12:45pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/6 "2003-11-20T12:45:49Z")

</div>

Yeah, but where’s the fun in that?

Easy answers, pfah!

---

<div class="post-metadata">

**Author:** ![Iteki](https://avatars.discourse-cdn.com/v4/letter/i/eada6e/32.png) [@Iteki](https://boards.straightdope.com/u/Iteki)\
**Post date:** [November 20, 2003, 1:24pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/7 "2003-11-20T13:24:35Z")

</div>

Oh god! **fezp** you beauty!  
That just worked so, so, nicely and has saved me literally hours!

Thanks to all the other answers too, they look very exciting and I will experiment with them at some stage 🙂 Appreciate the help all!

---

<div class="post-metadata">

**Author:** ![Iteki](https://avatars.discourse-cdn.com/v4/letter/i/eada6e/32.png) [@Iteki](https://boards.straightdope.com/u/Iteki)\
**Post date:** [November 20, 2003, 1:36pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/8 "2003-11-20T13:36:05Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![fezpp](https://avatars.discourse-cdn.com/v4/letter/f/da6949/32.png) [@fezpp](https://boards.straightdope.com/u/fezpp)\
**Post date:** [November 20, 2003, 2:09pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/9 "2003-11-20T14:09:09Z")

</div>

Just to confirm, are you wanting to end up with something like  
12345 123 123 123 123  
00123 012 001 012 123  
01234 012 012 001 012

or

12345123123123123  
00123012001012123  
01234012012001012

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.

---

<div class="post-metadata">

**Author:** ![Iteki](https://avatars.discourse-cdn.com/v4/letter/i/eada6e/32.png) [@Iteki](https://boards.straightdope.com/u/Iteki)\
**Post date:** [November 20, 2003, 2:24pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/10 "2003-11-20T14:24:31Z")

</div>

Some of my fields are blank, so I guess what I am going for is:

345 9089043 90456  
43 3456 3456 45646  
456564356 645646

I think your idea should still work tho, I will give it a shot now, thanks again!

---

<div class="post-metadata">

**Author:** ![Iteki](https://avatars.discourse-cdn.com/v4/letter/i/eada6e/32.png) [@Iteki](https://boards.straightdope.com/u/Iteki)\
**Post date:** [November 20, 2003, 2:29pm UTC](https://boards.straightdope.com/t/again-with-the-excel-access-questions/214282/11 "2003-11-20T14:29:46Z")

</div>

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 🙂

Whee! Learned something new today!