I recently posted about a more intuitive way to add spaces to a string in VBA. Jon Peltier of PeltierTech commented on a way to do this in a spreadsheet formula using the REPT() function.
Let’s assume we want to concatenate(or combine) cells A1 and B1 in another cell. We would also like to add 10 spaces between the two.
Usually in this situation I’ve used open quotes with spaces:
=A1&" "&B1
Thanks to Jon here’s a more accurate way to concatenate cells A1 and B1 with 10 spaces between, using the REPT() function:
=A1&REPT(" ",10)&B1
December 30th, 2004 at 5:44 am
For concatenating datas in two rows this formula is fine but what about concatenating 200 rows?
December 30th, 2004 at 11:56 am
Jeetendra,
It works just as any other formula would. As long as the formula is 1024 characters or less you’re good. Greater than 1024 and you get a “formula too long” error.
In this case you need to get creative and break the formula up to multiple cells, or reference the long parts of the formula from other cells instead of repeating the parts with many characters.
For instance putting
=REPT(” “,10) in cell A2 and referencing the cell A2 every time saves 12? characters each time you reference it.
I didn’t test it for 200, but it might look like this:
=A1&A2&B1&A2&C1&A2&D1…….