Adding various number of dots in excel

I have a lot of excel files looking like that:

Example:

enter image description here

My goal is to make it look like that:

result

To do that, I used very simple excel's function:

=F7&" "&G7&".........cat."&" "&H7&" times "&I7&CHAR(10)&F8&" "&G8&".........cat."&" "&H8&" times "&I8&CHAR(10)

The thing is, the number of dots placed before "cat" is not constant. It depends where the previous sentence ends and my formula doesn't take it into account - it always adds 9 dots, which means I have to add the rest of the dots manually.

Any ideas how to make it work? :D

I already mentioned the number of dots, and yes, the final result should be in one cell because I have to copy it to another file (dedicated online app in my office)
– amazingjerry
Sep 1 at 14:22

2 Answers
2

The REPT function can do this. Use LEN to calculate the length of what you're adding the dots to, then subtract that from the desired width of the result. That will repeat the dot enough times to fill the column. For example, if you want the text with dots to be 40 characters, right padded with .:

REPT

.

=F1&" "&G1&REPT(".",40-LEN(G1))&"cat."&" "&H1&" times "&I1&CHAR(10)&F2&""

Holy moly, works perfect :D Thanks for solution + explanation
– amazingjerry
Sep 1 at 14:21

=LEFT(A1 & REPT(".",22-LEN(A1))&"cat",25)

22 = fixed width - len("cat"), 25 - fixed width.

edit - i revised because my original answer was not correct but I see Comintern has posted a similar response since.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt