Is it possible to space two texts in centimeters in the same Excel cell?

Asked

Viewed 341 times

0

As the example of the figure below. i need to tabulate in a single cell two texts so that from one cell to the other (from the same column) the texts on the right are perfectly aligned (see column D).

inserir a descrição da imagem aqui

At the top of the figure: example of the result I have with the misalignment of page numbers (column D).

At the bottom: the same section, but with the formulas applied.

In cell D2 is informed the number of spaces of the distance in characters that the second text should be aligned. Here the ideal is that they were "centimeters" or something and not spaces.

I know there are numerous simple ways to do this, like using two columns or a font with characters of the same size in pixels, but in my case I need to solve like this, in a single cell and with any source that you want to apply.

This procedure should be added to a complex spreadsheet that dynamically manipulates a report that has links to several other spreadsheets and functionalities. The processing of the text is dynamic, that is, if I mark a topic or deselect, the entire report is automatically adjusted, the topics numbers are modified and the pages on which they are presented can be changed as well. For example, a topic "4. COSTS" that is on page 34, depending on what is done, can turn to topic "6. COSTS" and appear on page 59, and are numerous items, with topics and subtopics. The size of cells and their columns should preferably not be modified.

I tried some ways to get the result, researched, but unsuccessfully.

If you can do it, even if it is complex, it should be better and faster than studying and restructuring this spreadsheet.

Thanks in advance for the contributions or comments.

excel alignment characters

  • There is no way to do this only in Excel because it does not have a formula or function that calculates the length in pixels of any text in the font used. Maybe you can implement a length calculation function in pixels in C++, build a DLL and then use it in Excel... but I think you are trying to solve a problem that is not a problem: as you said yourself, there are other ways to do (and I think they’re better). The best of them? Use two separate columns. By the way, Excel exists for this: to manipulate data tabulated.

  • Hi Luiz, I understand, but as the texts in the subtopics are long, and the width for printing is at the desired limit, at the moment open a new column does not solve, I had already tried, more affects several other parts. I’m trying to deal with this detail without having to manipulate the rest that is quite complex. Cool the C++ tip, it’s a good possibility.

  • Man, I don’t understand why you can’t fix it. The print limit depends on the lengths of the columns, of course, but if you add a new one just reduce the length of the previous column (up because, it no longer has the content you put in the new column). Anyway, it was just a suggestion, because I think that this path that you are trying to follow is very difficult (to the point that maybe it is better to implement it in another tool/ language).

  • Hi Luiz, you are right, but there may be a solution, I am advancing other parts and then I will return to the problem, if I do not have an alternative way of doing, I will have a hard work ahead, but this is part... Thanks anyway, thank you!

1 answer

0


The solution I present is not exactly as I would like, but solved the problem simply and directly.

I added a TextBox (Activex control) at the end and on each line of the home page that can have text to reference the page (with the same spacing in number of lines in my case, between the controls).

In the properties passed to transparent, aligned all and changed the source to the same active in Excel (because I believe that there is no way to do this automatically, recognize the source of the cell linked), After that, I grouped everything to be able to adjust the block to the sides easily for when it is the case.

On the estate Linkedcell pointed to the cell with the text of the page number to be displayed.

With this, the lines that feature page number have sues TextBox dynamically updated, just like the others are "empty" and do not appear when they do not have a page number.

See the example below:

inserir a descrição da imagem aqui

In the first line I highlighted the TextBoxthat each line has at the end, as well as at the last two lines, to show that all are there, the other lines appear as they should appear for printing (the controls are also there). Because they are transparent and have no text, in fact the two final lines appear "blank" as they should appear, here I highlighted to better exemplify.

That’s it!

  • Why does spacing work in the Textbox and not work in the cell? You say you "changed the font". Which one? Curious that in your question you said "and with any font you want to apply". (it occurred to me now that you might have added Textbox only for line numbers, on the right side of the text columns; if so, explain better - with illustrations - because it is not properly clear).

  • Hi Luiz, I didn’t do the spacing in the Textbox, I just put it at the end of the line, so in the print the page number appears as if it was originally in the printed cell, it’s just overlay. I’ll review the answer.

  • 1

    Luiz, see if it’s good now...

Browser other questions tagged excel alignment characters

You are not signed in. Login or sign up in order to post.