Forum Discussion
REPT function is not working correctly (for me).
I have been attempting to use the REPT function on a few spreadsheets lately, and it isn't functioning like I would expect. Can someone please help me figure out what is affecting this?
I typically use this function when I'm using Excel to organize a TON of data and use a combination of "Merge and Center" with the "Rotate Text Up" formatting options to "mark out" a range of horizontal data with a vertical column "marker".
The steps:
1.) Create a new blank Excel workbook.
2.) Select cell A1 to A1000 (or so).
3.) Select "Merge and Center" to merge all selected vertical cells into one.
4.) Select the "Rotate Text Up" formatting option from the Orientation drop-down selection.
5.) In the merged cell, type something like =REPT("<----- THIS TEST TEXT WILL NOT WORK ----->", 50).
6.) See how many times your repeated text shows up in the vertical column.
Mine generates the text about 24 1/2 times before it just ends and doesn't even come close to filling the entire, vertically-merged, column. No matter how many times I use for the repeat function (50 in this example but I've used less/more), I don't ever get past the 24 1/2 times. I am nowhere close to the character limit in a cell (which if I remember correctly is just over 32,000) and I can't figure out why this isn't working now. I have used this many times in the past and it's always worked, and I can open existing spreadsheets of mine where it is working as expected. Why can't I get this to work now?
Thanks in advance for your help with this!
(P.S. I can't see a place where I can attach a spreadsheet to this post, otherwise I would have attached my example to save you all some steps and provide another "debugging" avenue.)
It has nothing to do with the REPT function, nor with the merged cells or the rotated text.
Although a cell can contain up to 32,767 characters, only up to 1,024 characters will be displayed in the cell.
The text that you repeat has 42 characters, so it will be displayed 1,024/42 ~ 24.4 times.
3 Replies
It has nothing to do with the REPT function, nor with the merged cells or the rotated text.
Although a cell can contain up to 32,767 characters, only up to 1,024 characters will be displayed in the cell.
The text that you repeat has 42 characters, so it will be displayed 1,024/42 ~ 24.4 times.
Thank you, Nikolino for your input and suggestions! However, it was HansVogelaar who landed on the issue. I had no idea there was a character display maximum (caveat below) and that's what I keep running into.
I reviewed the older spreadsheets that have data organized in the same way, and they either had a row quantity and a text height that ended up under the 1,024 character display limit, or they had the "merged" rows split up into smaller "segments" where the character limit was right at the end of one of the repeats. The borders between them removed, the text lined up, and the sheets are all protected and the columns aren't "selectable" (until I turn off the protection). This means that, although I have no memory of this whatsoever, I apparently ran into this limit before, understood what was happening, and "fixed" it by configuring/merging the rows in this second fashion, adjusting them all so they looked "perfect", repeated exactly as needed, and then protected everything so it worked and looked beautiful! I have 12 or so spreadsheets from 15 years ago where I did this, and I seriously do not have a single memory of any of this
. Sorry for taking up your time everyone. And, thanks Hans for pointing out something I have apparently forgotten (I'm getting too old I guess)!
The issue you are experiencing with the REPT function not repeating the text as expected might be related to the way Excel is handling merged cells with rotated text. When text is rotated, the cell height is limited, and it might not display as many repetitions as expected.
Instead of using the "Merge and Center" with "Rotate Text Up" formatting options, you can achieve a similar visual effect by adjusting the row height manually without merging the cells. Here are the steps:
1. Select the Range:
Select the range of cells where you want the repeated text.
2. Adjust Row Height:
Right-click on the selected cells.
Choose "Row Height."
Set the desired row height that accommodates the repeated text.
3. Enter the REPT Formula:
In the top cell of the selected range, enter the REPT formula.
For example, if you want to repeat the text 50 times:
=REPT("<----- THIS TEST TEXT WILL WORK ----->", 50)
4. Copy Formula Down:
Drag the formula down to fill the desired number of cells.
This way, you manually adjust the row height to allow for the repeated text without using the "Merge and Center" option, which can affect the display of repeated text in rotated cells.
Additionally, if you still encounter issues, consider checking if any conditional formatting or other settings are affecting the display. It's possible that certain formatting options might interfere with the behavior of merged and rotated cells. Using the manual row height adjustment method should provide more control over the visual appearance of the repeated text. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
