REPT

returns a text string which includes repetitions of another text string.

Syntax:

REPT(text, number)

returns number copies of text, joined together.

Example:

REPT("ha", 7)

returns hahahahahahaha!

REPT(ROUND(RAND()*10), 3)

returns nnn where n is a random digit repeated three times, while:

CONCATENATE(ROUND(RAND()*10), ROUND(RAND()*10), ROUND(RAND()*10))

returns xyz where x, y, z are three randomly independent digits.

Application:

Here is an application of using the REPT function for data normalization and display.

Let's say you have a list of employee IDs, and for data entry consistency, you need to ensure that every ID is exactly 10 characters long. Some IDs were entered with fewer characters, and they need to be padded with leading zeros.

The Data:

	Employee ID
	A
1	12345
2	987654
3	543210987
4	10
5	9999999999

The Goal:

Pad the Employee ID with leading zeros so that every ID has a total length of 10 characters.

The Formula:

In a new column (let's say Column B), you would enter the following formula for the first employee ID, assuming the Employee ID is in cell A1:

REPT("0", 10-LEN(A1)) & A1

Explanation of the Formula:

LEN(A1): This part of the formula calculates the length of the text in cell A2. For the first row, LEN("12345") will return 5.
10-LEN(A1): This calculates how many zeros are needed. For the first row, this will be 10 - 5 = 5.
REPT("0", 10-LEN(A1)): This is the core of the solution. It repeats the character "0" a number of times equal to the result of the previous calculation. In the first row, it will repeat "0" five times, resulting in "00000".
& A1: The ampersand (&) is the concatenation operator. It joins the result of the REPT function with the original employee ID in cell A2. The result will be "00000" joined with "12345", which gives "0000012345".

The Resulting Table:

	Employee ID	Formatted Employee ID
	A	B
1	12345	0000012345
2	987654	0000987654
3	543210987	0543210987
4	10	0000000010
5	9999999999	9999999999