Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
I need to combine two text cells in Excel into one character string with
filler dot characters in the middle to produce a character string of a fixed length in inches that will end up in a Microsoft Word table. The fonts are proportional and the same in both Excel and the Word table. For example Left string - €śAAAAAA€ť Right string - €śBBBBB€ť Concatenated string with filler dots - €śAAAAAA€¦€¦BBBBB€ť The only way I have found so far is to build the string putting one dot at time in the middle until the string wraps in a cell whos width is set equal to the Word table cell width. By testing the height of the cell I can tell when the cell wraps and the length has been exceeded. Is there a better way to accomplish this? -- russ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
On 12 Feb, 14:19, Russ wrote:
I need to combine two text cells in Excel into one character string with filler dot characters in the middle to produce a character string of a fixed length in inches that will end up in a Microsoft Word table. The fonts are proportional and the same in both Excel and the Word table. For example Left string - "AAAAAA" Right string - "BBBBB" Concatenated string with filler dots - "AAAAAA......BBBBB" The only way I have found so far is to build the string putting one dot at time in the middle until the string wraps in a cell who's width is set equal to the Word table cell width. By testing the height of the cell I can tell when the cell wraps and the length has been exceeded. Is there a better way to accomplish this? -- russ Public Sub CreateString() Dim strString1 As String Dim strString2 As String Dim intRequiredLength As Integer Dim strFinalString As String strString1 = "AAAAAA" strString2 = "BBBBBB" intRequiredLength = 15 Do Until Len(strString1) + Len(strString2) = intRequiredLength strString1 = strString1 & "." Loop strFinalString = strString1 + strString2 End Sub hth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
Unless I am mistaken your answer only works for fixed length fonts. The
problem is I am using proportional fonts where the length of the string is character dependent. -- russ "Keith74" wrote: On 12 Feb, 14:19, Russ wrote: I need to combine two text cells in Excel into one character string with filler dot characters in the middle to produce a character string of a fixed length in inches that will end up in a Microsoft Word table. The fonts are proportional and the same in both Excel and the Word table. For example Left string - "AAAAAA" Right string - "BBBBB" Concatenated string with filler dots - "AAAAAA......BBBBB" The only way I have found so far is to build the string putting one dot at time in the middle until the string wraps in a cell who's width is set equal to the Word table cell width. By testing the height of the cell I can tell when the cell wraps and the length has been exceeded. Is there a better way to accomplish this? -- russ Public Sub CreateString() Dim strString1 As String Dim strString2 As String Dim intRequiredLength As Integer Dim strFinalString As String strString1 = "AAAAAA" strString2 = "BBBBBB" intRequiredLength = 15 Do Until Len(strString1) + Len(strString2) = intRequiredLength strString1 = strString1 & "." Loop strFinalString = strString1 + strString2 End Sub hth |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
As far as i know the Len function returns a number based only on the
no of characters, irrespective of length of the character. Never used proportional fonts so can't be sure |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
Hi Russ
I don't think there is much else you can do. Even if some api functions like GetTextMetrics which i am not familiar with, would tell you how wide your text is, you still would have to calculate the maximum width beforehand, get the width of a space und add as many spaces at once to get the perfect width. I even doubt, whether adding spaces the way you do in Excel, will always show the desired effect in Word. Does it? Then I have once again learned something. How about leaving it all to Word. Wouldn't be any more elegant, though, but then you would know, whether the text fits according to Word's way of calculating it. In Word you would use Range.ComputeStatistics(wdStatisticLines). But, i'm sure, there was no need of telling you that. Have a nice day. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
In general I'd guess that this would be very difficult. However, Word's tabs
let you use certain leader lines, which bail you out in this situation. In the Word table, set up a tab in the cell with dots for the tab leader line. From Excel, insert AAAAAA and the tab character and finally BBBBB. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Russ" wrote in message ... I need to combine two text cells in Excel into one character string with filler dot characters in the middle to produce a character string of a fixed length in inches that will end up in a Microsoft Word table. The fonts are proportional and the same in both Excel and the Word table. For example Left string - "AAAAAA" Right string - "BBBBB" Concatenated string with filler dots - "AAAAAA..BBBBB" The only way I have found so far is to build the string putting one dot at time in the middle until the string wraps in a cell who's width is set equal to the Word table cell width. By testing the height of the cell I can tell when the cell wraps and the length has been exceeded. Is there a better way to accomplish this? -- russ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
Hi Helmut,
Thanks for you reply. Actually I do leave it to Word when I construct the finished product. However, all the text is entered in Excel over time and I want to be sure everything fits before I, once a year, produce the finished product. The technique I use works quite well but it is rather kluggy. I guess I will just have to live with it. Thanks again. -- russ "Helmut Weber" wrote: Hi Russ I don't think there is much else you can do. Even if some api functions like GetTextMetrics which i am not familiar with, would tell you how wide your text is, you still would have to calculate the maximum width beforehand, get the width of a space und add as many spaces at once to get the perfect width. I even doubt, whether adding spaces the way you do in Excel, will always show the desired effect in Word. Does it? Then I have once again learned something. How about leaving it all to Word. Wouldn't be any more elegant, though, but then you would know, whether the text fits according to Word's way of calculating it. In Word you would use Range.ComputeStatistics(wdStatisticLines). But, i'm sure, there was no need of telling you that. Have a nice day. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the length of proportional text strings
Hi Jon,
Yes Word tabs do the job very nicely once I load my Excel text (without the dots in the middle). The clumsy way I check in Excel does always manage to make sure it fits when i get to Word. Thanks for you help. -- russ "Jon Peltier" wrote: In general I'd guess that this would be very difficult. However, Word's tabs let you use certain leader lines, which bail you out in this situation. In the Word table, set up a tab in the cell with dots for the tab leader line. From Excel, insert AAAAAA and the tab character and finally BBBBB. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Russ" wrote in message ... I need to combine two text cells in Excel into one character string with filler dot characters in the middle to produce a character string of a fixed length in inches that will end up in a Microsoft Word table. The fonts are proportional and the same in both Excel and the Word table. For example Left string - "AAAAAA" Right string - "BBBBB" Concatenated string with filler dots - "AAAAAA..BBBBB" The only way I have found so far is to build the string putting one dot at time in the middle until the string wraps in a cell who's width is set equal to the Word table cell width. By testing the height of the cell I can tell when the cell wraps and the length has been exceeded. Is there a better way to accomplish this? -- russ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
Finding text strings in complex situations | Excel Discussion (Misc queries) | |||
Finding 13 character codes in text strings | Excel Worksheet Functions | |||
Finding Duplicate text strings with a single column | Excel Worksheet Functions |