Used this for work, thought someone else might be able to...
If you are using a fixed-width font to make a report, and you need to put in a variable number of spaces based on the contents, you can use rept(" ",10-len(A1)), for example. However, if you are using concatenation and vlookup for a large number of values, this can get kind of crazy.
Paste this function into your vba page:
[pre]
[/pre]
you can now put =PAD(A1," ",10)
if you want to pad on the right, you can put =PAD(A1," ",10,TRUE)
If you are using a fixed-width font to make a report, and you need to put in a variable number of spaces based on the contents, you can use rept(" ",10-len(A1)), for example. However, if you are using concatenation and vlookup for a large number of values, this can get kind of crazy.
Paste this function into your vba page:
[pre]
Code:
Function PAD(text As Variant, char As String, length As Integer, Optional padright As Boolean = False)
Dim tlength As Integer
Dim returnstring As String
tlength = Len(text)
returnstring = String(length - tlength, char) & text
If padright = True Then
returnstring = text & String(length - tlength, char)
End If
PAD = returnstring
End Functionyou can now put =PAD(A1," ",10)
if you want to pad on the right, you can put =PAD(A1," ",10,TRUE)
