Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Left padding or Right Padding in VBA 2

Status
Not open for further replies.

mveera

Programmer
Nov 6, 2002
81
US
Hi,

Is there any method whihc can add leading or trailing spaces to a text. i want something which is an exact opposite of what we do in LTrim or RTrim. We have LPad and RPad methods in Oracle. Is there something similar to this in VB or VBA.

Thanks
Veera
 
I haven't tried it, but this should work:

myStr = lPad(8,"hi")

function lPad(num, str)
do while len(str)<num
str = &quot; &quot; & str
loop
lPad = str
end function
 
Thank You.

So there is built in function in VB or VBA to do the padding.

Thanks
Veera
 
Well, it's not &quot;built-in&quot; but you just wrote your own function. Of course you'd have to change the string concatentation around to write a rPad function....
 
You can use the worksheet function REPT as follows

mStr = &quot;Hello&quot;
lPad = WorksheetFunction.Rept(&quot; &quot;, 3) & mStr
rPad = mStr & WorksheetFunction.Rept(&quot; &quot;, 3)

lPad = &quot; Hello&quot;
rPad = &quot;Hello &quot;

HTH Rgds
~Geoff~
 
and to add to the jumble of options,

Function LPad(s as string, PadLen as integer) as string
LPad=left(&quot; &quot;,PadLen-Len(s))+s
end function

(but actually, I like Geoff's REPT better, because the maximum pad length is not limited to the number of spaces in the string constant, as above)
Rob
 
The LPad and RPad function in Oracle will also truncate the string if the number is less than the number of characters. For example, both:

SELECT rpad('abcdefgh',3) FROM dual;
and
SELECT lpad('abcdefgh',3) FROM dual;
will return
abc

So, I'll add one more option:

Code:
Function LPad(str As String, num As Long) As String
   If Len(str) > num Then
      LPad = Left(str & Space(num), num)
   Else
      LPad = Space(num - Len(str)) & str
   End If
End Function

Function RPad(str As String, num As Long) As String
   RPad = Left(str & Space(num), num)
End Function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top