CODEWITHSUNDEEP
vbaexcelformula
amazingjerry
amazingjerry

Reputation: 13

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

Upvotes: 1

Views: 318

Answers (2)

Doug Coats
Doug Coats

Reputation: 7107

=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.

Upvotes: 1

Comintern
Comintern

Reputation: 22185

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 .:

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

Upvotes: 2

Related Questions