Adding spaces to a text field

[Jason Trolian]

I have a text filed that contains numbers that are a
reference number for our system. I'm trying to link that
table in access but first I need to format the
field "dbtrno" so it is always 6 spaces and right
justified.

field currently reads: "1234"
needs to be: " 1234"

also there are some 3 digit numbers mixed in:

field currently reads: "123"
needs to be: " 123"

Any suggestions? Thank you in advance,

Jason Trolian

 

[Chip Pearson]

Jason,

You can use a function like the following:

Function LeftPad(S As String, L As Integer) As String
LeftPad = String$(L - Len(S), " ") & S
End Function

Then call this function as
Dim S As String
S = LeftPad("123",6)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Harald Staff]

Hi Jason

Select the cells and run this little macro:

Sub SixChars()
Dim Cel As Range
For Each Cel In Selection
If Len(Cel.Value) < 6 Then _
Cel.Value = String(6 - _
Len(Cel.Value), Chr(32)) & _
Cel.Value
Next
End Sub

Note: The number cells must be text formatted, or Excel will remove the
leading spaces again.

 

[Jason Trolian]

That worked perfectly, thank you.

JT

-----Original Message-----
Hi Jason

Select the cells and run this little macro:

Sub SixChars()
Dim Cel As Range
For Each Cel In Selection
If Len(Cel.Value) < 6 Then _
Cel.Value = String(6 - _
Len(Cel.Value), Chr(32)) & _
Cel.Value
Next
End Sub

Note: The number cells must be text formatted, or Excel will remove the
leading spaces again.

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Jason Trolian" <[email protected]> skrev i melding



.