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?

 

[Peter_SSs]

@Peter_SSs I think that you have missed post number 4

Thanks Mark, yes I did miss that.

 

[Peter_SSs]

This will not change the number from 1 to 3.

With your Excel version I think that you would need to employ a user-defined function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function tseklee(r As Range) As String
  Dim i As Long
  
  For i = 1 To r.Cells(2).Value
    tseklee = tseklee & vbLf & r.Cells(1).Value & i & r.Cells(3).Value
  Next i
  tseklee = Mid(tseklee, 2)
End Function

tseklee.xlsm
	A	B	C	D
1	red	3	dots	red1dots red2dots red3dots
2	red	1	dots	red1dots
3
4	red	0	dots
Sheet1

Cell Formulas
Range	Formula
D1:D4	D1	=tseklee(A1:C1)

 

[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)

 

[tseklee]

Is there any chance that this result is accomplished only through a formula, avoiding the use of VBA as i don't think i then will be able to use this method with my android phone or if i give the file to someone using an open-source office.

 

[Peter_SSs]

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

Hi Scott
Quite similar to mine but that leaves a hanging Chr(10) on the end if b>1 which I suspect would be unwanted.

Is there any chance that this result is accomplished only through a formula

1. Are you still using Excel 2007?
2. Is there a maximum value that could be in column B?

 

[tseklee]

1. Are you still using Excel 2007?
2. Is there a maximum value that could be in column B?

1. Yes i am using Excel 2007, ocassionally LibreOffice since it can run portable and OfficeSuite from MobiSystems on my phone.
2. Theoreticaly it should work with numbers less than 1000 or 999. But since i am a newbie with excel i will propably copy and paste the formula to fill also other needs, in which case the limitation/s will manifest itself. I tried to answer as best as i could, if i understood the question right.

 

[Peter_SSs]

Theoreticaly it should work with numbers less than 1000 or 999.

If the value in column B can be as big as 1000 then it will not be feasible to do it with standard worksheet functions with your version. The formula would need to be like this, with 1000 of those coloured sections near the start (each with two incremental numbers in them) and 1000 of the matching double quotes and closing parentheses at the end!!
On the plus side it would need only one of the SUBSTITUTE functions wrapped around it.

=SUBSTITUTE(IF(B1>0,CHAR(10)&A1&1&C1&IF(B1>1,CHAR(10)&A1&2&C1&IF(B1>2,CHAR(10)&A1&3&C1 .............,""),""),""),CHAR(10),"",1)

 

[Scott Huish]

Hi Scott
Quite similar to mine but that leaves a hanging Chr(10) on the end if b>1 which I suspect would be unwanted.

Good call. I love your technique of adding the delimiter at the beginning and then starting at the 2nd character.