Make set length of text value?

[brianburen]

I have a userform that shows the results of many values in 20 text strings

such as:

textlabel1= "firstname1 & " " & lastname1 & " " & housenumber1 & " " & street name1"

textlabel2= "firstname2 & " " & lastname2 & " " & housenumber2 & " " & street name2"

I want to set the text lengths of ... to be x amount letters always, so on the form it all lines up nicely


How do i format the length of the text to be 6 letters even if it is a 4 letter street and the extra 2 letters be spaces?

I tried:

firstname1=Format(ws.range("A1").Text, "XXXXXX")

but it doesnt work

 

[tigeravatar]

brianburen,

Something like this?

    firstname1 = ws.Range("A1").Text
    If Len(firstname1) < 6 Then firstname1 = firstname1 & String(6 - Len(firstname1), " ")

 

[nancymk]

Could you do =left(A1&" ",6)

 

[nancymk]

between the quotes it has 6 spaces. When I hit post, it took out the extra 5

 

[brianburen]

Thanks for the quick replies!
I'm gonna try the suggestions and report back.

 

[brianburen]

So far neither suggestion is working, but I am not getting any errors either.

I'm gonna fiddle with it a while....

 

[brianburen]

on further evaluation it seems both of these suggestions ARE in fact working, but the width of the " " space is not equal to the space of an "A" or "5".
Is there a font that has equal horizontal spacing for spaces, caps letters, and numbers?

 

[brianburen]

on further evaluation it seems both of these suggestions ARE in fact working, but the width of the " " space is not equal to the space of an "A" or "5".
Is there a font that has equal horizontal spacing for spaces, caps letters, and numbers?

I guess I should note that all letters are in CAPS.

so if i replace the " " with "X", both suggestions work, except I dont want the X's

 

[tigeravatar]

brianburen,

You could use a tab character instead of spaces:

firstname1 = ws.Range("A1").Text & Chr(9)

If the alternate font route is what you'd prefer, then Consolas is a uniform size font you could try.

 

[brianburen]

brianburen,

You could use a tab character instead of spaces:

firstname1 = ws.Range("A1").Text & Chr(9)

If the alternate font route is what you'd prefer, then Consolas is a uniform size font you could try.

Ok it puts a set amount of characters after the text. how can i make it determine if say the firstname1 was only 4 characters, to add 5 characters
and if firstname2 was only 7 characters, it would add 2?