Rept or Concat or both? Help please

[tseklee]

Hello i am trying to join 3 cells into one and repeat the result as many times given in a specific cell as number. Example:

A	B	C	D
red	3	dots	red3dots red3dots red3dots

I have written the desired result under D.
I want the result to be limited in one cell and not expand below.
The best formula i have written so far is:
=REPT(A1,IF(B1>0,B1,CHAR(13)&CHAR(10)))
and all i see is: redredred

Can someone help me please?

 

[Scott Huish]

I think for that version it's going to take a UDF:

Function CombineandIncrement(a As String, b As Long, c As String)
Dim x As Long, s As String
For x = 1 To b
    s = s & a & x & c & IIf(b > 1, Chr(10), "")
Next
CombineandIncrement = s
End Function

Book2
	A	B	C	D	E
1	red	3	dots		red1dots red2dots red3dots
2	red	4	dots		red1dots red2dots red3dots red4dots
3	green	1	dot		green1 dot
Sheet1

Cell Formulas
Range	Formula
E1:E3	E1	=CombineandIncrement(A1,B1,C1)

 

[Sam_D_Ben]

Please check,

=REPT(IF(B1<>0,A1&B1&C1,0),3)

Thanks,
Sam

 

[tseklee]

Please check,

View attachment 101479
=REPT(IF(B1<>0,A1&B1&C1,0),3)

Thanks,
Sam

I had to change the last "3" from your formula to "B1" like:
=REPT(IF(B1<>0,A1&B1&C1,0),B1)

But the result is printed in one line, like so: Red3dotsRed3dotsRed3dots

Instead of this i want it to change line within the same cell though, that's why i was testing CHAR(10) and CHAR(13) commands.

 

[tseklee]

Oh i'm sorry i misstyped my desired result. It should look like this:

A	B	C	D
red	3	dots	red1dots red2dots red3dots

So it should not just repeat the result according to the number found in cell B1 but instead add from 1 till it reaches the number found in B1.

 

[awoohaw]

Mr Excel Questions 71.xlsm
	A	B	C	D
1	A	B	C	D
2	red	3	dots	red1dots red2dots red3dots
tseklee

Cell Formulas
Range	Formula
D2	D2	=TEXTJOIN(CHAR(10)&CHAR(13),TRUE,A2&SEQUENCE(1,B2)&C2)

 

[awoohaw]

The above won't work either, as for the 2nd time ive forgotten you are on 2007. My apologies.

 

[Sam_D_Ben]

=CONCAT(REPT((A1&B1&C1&CHAR(10)),B1))

>Please enable text wrapping
>Please insert CHAR(10) where you want the line-breaks
>Adjust the row height manually

Thanks
Sam

 

[Peter_SSs]

=CONCAT(REPT((A1&B1&C1&CHAR(10)),B1))

The OP's profile shows Excel 2007 so will not have the CONCAT function. In any case, the CONCAT function in that formula does nothing since the values are already concatenated by the rest of your formula.
One other point though is that formula will put a CHAR(10) after the final C1 value which may be an issue.
My suggestion is

=IF(B1>1,REPT(A1&B1&C1&CHAR(10),B1-1),"")&A1&B1&C1

 

[MARK858]

@Peter_SSs @Sam_D_Ben I think that you have missed post number 4