Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text format alignment problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text format alignment problem
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text format alignment problem
Steve wrote:
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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text format alignment problem
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. -----Original Message----- 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 -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alignment problem with spreadsheet | New Users to Excel | |||
Format, Cells, Alignment. | Excel Discussion (Misc queries) | |||
Format Axis Alignment | Charts and Charting in Excel | |||
Dollar format alignment | Excel Discussion (Misc queries) | |||
vertical text alignment problem when printing | Excel Discussion (Misc queries) |