zero fill

[Scott]

I have a column numbers stored as text:

2667-1010
3189-358
5893-52
5893-1
85-1863
386-685

What I want is all numbers to be in this format:

02667 1010
03189 0358
05893 0052
05893 0001
00085 1863
00386 0685

Is there any way that I can do this?
Thanks.

 

[Wayne Morgan]

Run an Update Query on the field. Update it to:

Format(Left([FieldName], Instr([FieldName],"-")-1), "00000") & " " &
Format(Mid([FieldName], InStr([FieldName], "-")+1), "0000")

Example:
UPDATE Table1 SET Table1.FieldName = Format(Left([FieldName],
Instr([FieldName],"-")-1), "00000") & " " & Format(Mid([FieldName],
InStr([FieldName], "-")+1), "0000");

 

[Rudy W.]

With a Update query you can do the trick. A possible
solution could be:

UPDATE tblNumbers SET tblNumbers.[number] = Format(Val
(Left([tblNumbers]![number],InStr([tblNumbers]![number],"-
")-1)),"00000") & " " & Format(Val(Right([tblNumbers]!
[number],Len([tblNumbers]![number])-InStr([tblNumbers]!
[number],"-"))),"0000");

Rudy W.

 

[Allen Browne]

Use Instr() to get the position of the hyphen.
Then Left() and Mid() to parse the two numbers.
Then Format() to generate the output.

This kind of thing:

Format(Left([MyField], Instr([MyField], "-" - 1), "00000") & " " &
Format(Mid([MyField], Instr([MyField], "-" + 1), "0000")

 

[Kevin Sprinkel]

The following function should do the trick. Please note
that there is no error handling. I've tested it, but I'd
suggest you add a field to your table, and use an update
query to change the new field's value to:

=FillWithZeros([youroriginalfieldname]) and verify that
all are correct before deleting your original field.

Function FillWithZeros(strValue As String) As String

Dim I As Integer, intPosition As Integer

' find position of hyphen
intPosition = InStr(strValue, "-")

' fill initial zeros
For I = intPosition To 5
strValue = "0" & strValue
Next

' fill final zeros
For I = Len(strValue) To 9
strValue = Left(strValue, 6) & "0" & _
Right(strValue, I - 6)
Next

' Strip hyphen, add space; assign to function value
FillWithZeros = Left(strValue, 5) & " " & _
Right(strValue, 4)

End Function

HTH
Kevin Sprinkel