Add Spaces to a Cell – REPT()

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