Align-left and Align-Right is irrelevent (I think)
because "alignment" is a paragraph property, not a string property.
What you're saying is that you need the string padded
with spaces either on the left or right...
So, by example, if you were padding with underscores (_),
then the results you're looking for are:
______
X_____
XX____
XXX___
XXXX__
XXXXX_
XXXXXX
If your value is in Cell B1, then a Cell Function would be:
=B1&REPT("_",6-LEN(B1))
(or using " " to pad with spaces.
In VBA, your solution would be:
StrOut = StrOut & StrDelim _
& Cells(R, 33).Value & _
Application.WorksheetFunction.Rept(" ", 6 - Len(Cells(R, 33).Value))
Paul
________________________________
From: Erick C <boricua2...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, January 7, 2011 10:10:09 AM
Subject: $$Excel-Macros$$ Need Help Getting Text To Align Left
Hello everybody,
I am in desperate need of some assistance, and I am hoping someone may
be able to give me a hand. I am still a bit of a novice when it comes
to vba code writing, so I am hoping that it may just be something that
I am not aware of.
I have some code that makes a string of text using a row of data in
Excel. I need a few parts of the text to be formatted to fit 6
spaces, regardless of how few letters or numbers are in the cell in
Excel, since many times there are only 2 or 3 characters in the cell
(ex. AA or A1). Sometimes the cell may even be blank.
Right now I am using the following:
StrOut = StrOut & StrDelim & Format(Cells(R, 33).Value, "@@@@@@")
This gets 6 spaces if there is any text in the Excel cell, but
everything is aligned on the right. I need to have the text to be
left justified in order to be able to import it into another system.
Also, if the Excel cell is blank it does not give me six blank spaces
in the text, which is what I would need.
Does anybody have any tips or suggestions that may help me out? I
have been looking everywhere online for something that may be able to
help with no success.
Thanks.
Erick
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
