excelvbaexcel-formula

REPT function in Excel

Please I need excel to repeat text in a cell 2x That is REPT(text, 2) IF the text is not repeated, but if it is already repeated 2x, it should not and leave it that way.

I tried using this formular

=IF(B2=REPT(B2,1), B2=REPT(B2,2), B2=B2)

It gives me False, then 0 gives me true.

Excel formula or VBA would be greatly appreciated.

Solution

  • For any cell not B2:

    =IF(LEFT(B2,LEN(B2)/2)=RIGHT(B2,LEN(B2)/2),B2,REPT(B2,2))

    This formula allows any symbol at the center of B2 (like xxxYxxx). To avoid this effect, use modified Tim's formula:

    =IF(B2=REPT(LEFT(B2,LEN(B2)/2),2),B2,REPT(B2,2))