Text format alignment problem

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am making new strings to include a hyphen like this:

strC = strA & "-" & strB

Length strB is not fixed but is limited to maximum 6
characters. Max length strA is not limited but normally
would not exceed 10.

I would like to align my new strings in cells so that the
hyphens are vertically aligned, ideally if possible from
the right, sort of akin to decimal alignment.

I would also be interested to do similar say the "-"
aligned into the 10th character position from left.

TIA,
Steve
 
Something like the following should help, basically you
create for each cell a content that is 17 characters long,
just make the cell text alignment to center and then you
are done.

Dim strA As String * 10
Dim strB As String * 6
Dim strC As String


strB = strDataB
strC = Mid(strA, 1, Len(strA) - Len(strDataA)) & strDataA
& "-" & strB
Debug.Print strC

regards
KM
 
Steve said:
I would like to align my new strings in cells so that the
hyphens are vertically aligned, ideally if possible from
the right, sort of akin to decimal alignment.
Click to expand...

If you are using a proportional font I think you will be out of luck.
With a fixed width font you could programmatically set the indent of
the cells (but it doesn't seem to be perfect):

Sub IndentCells()
Dim C As Range
For Each C In Selection.Cells
C.IndentLevel = 11-InStr(C.Value, "-")
Next
End Sub

The alternative would be to find the width of the string before the "-"
using an auto-sizing textbox and then to format the cell with an
appropriate number of filling space characters before the text using a
custom format like
_ _ _ @
to give 3 spaces before the text.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Kevin,

Fantastic, why did I waste so much time messing with
Custom formats!

At first I could not put strC into a cell or see it in a
Msgbox, even though it printed perfectly to the
intermediate window. Might that be because of intial
Ascii codes being zero?
Whatever, if I fill strA with 10 spaces everything works
fine.

The only other thing I find I need to do is to make the
font in cells receiving strC non proportional, say Courier
New.

Many thanks,
Steve

PS
Thanks also Bill,
I was just about to post the above when I saw your message
just come in. Except for the proportional font issue
Kevin's approach works fine. However I might have another
look at Custom formats and spaces as you suggest.
 
Back
Top