# How much TEXT can I put in a cell or a large merged cell?

#### SperryRed

##### New Member
I need to get about 2800 charaters and how can I do it?

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Oorang

##### Well-known Member
Just put them in.
According to this:
http://office.microsoft.com/en-us/assistance/hp051992911033.aspx
Excel's maximum cell content is 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

I would advise caution however as I have noticed some odd behaviour out of really huge cells. What are you doing? Perhaps someone could suggest an alternative.

#### Norie

##### Well-known Member
It depends what you mean.

I entered this formula in one cell, A1.

=REPT("X",2800)

And this in another.

=LEN(A1)

The 2nd formula indicated that I had created a formula that returned 2800 Xs.

#### erik.van.geit

##### MrExcel MVP
Hi,

here is a testroutine
Code:
``````Option Explicit

Sub count_caracters_in_cell()
'Erik Van Geit
'061019
Dim cell As Range
Dim nr As Variant
Dim x As Integer
Dim temp As String

Set cell = Sheets(1).Range("A1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Do
nr = Application.InputBox("fill in number of caracters to be tested (20,30,...,3000,...)", "# caracters to be put in one cell ", 1)
If nr = False Then GoTo skip
If nr Mod 10 <> 0 Or nr < 20 Then MsgBox "only numbers ending with 0 and > 10 ", 48, "20,30,..."
Loop While nr Mod 10 <> 0 Or nr < 20

With cell
.EntireColumn.ColumnWidth = 120
.RowHeight = 350
.WrapText = True
.Font.Size = 4 'change to test
.VerticalAlignment = xlVAlignTop
.Offset(1, 0).FormulaR1C1 = "=LEFT(R[-1]C,10) & "" "" & RIGHT(R[-1]C,10)"
.Offset(2, 0).FormulaR1C1 = "=LEN(R[-2]C)"
.Offset(3, 0) = "blue = 10, green = 100, red = 1000"
.Offset(1, 0).Select
End With

'temp = temp & Application.Rept("o", (nr - 20))
temp = "STARTooooo" & Application.Rept("o", (nr - 20)) & "oooooooEND"
cell = temp

If nr < 2000 Then
For x = 10 To nr Step 10
With cell.Characters(Start:=x, Length:=1).Font
.Size = 10
.ColorIndex = 8
End With
Next x
End If

For x = 100 To nr Step 100
With cell.Characters(Start:=x, Length:=1).Font
.Size = 11
.ColorIndex = 4
End With
Next x

For x = 1000 To nr Step 1000
With cell.Characters(Start:=x, Length:=1).Font
.Size = 12
.ColorIndex = 3
End With
Next x

With ActiveWindow
.Zoom = True
.ScrollRow = 1
.ScrollColumn = 1
End With

MsgBox "number of caracters put in the cell: " & " " & Len(temp) & Chr(10) & _
"number of caracters counted in the cell: " & " " & Len(cell) & Chr(10) & _
"""START"" at the beginning & ""END""  at the end to test if everything resides in memory" & Chr(10) & _
"in the cell below please find the first 10 & last 10 caracters found in cell A1", 64, "caracters"

skip:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayScrollBars = False
End With

End Sub``````
but why would you need to put such an amount of info into one single cell? Aren't there enough cells ?

kind regards,
Erik

Replies
8
Views
474
Replies
4
Views
59
Replies
0
Views
325
Replies
3
Views
363
Replies
2
Views
246

1,141,155
Messages
5,704,626
Members
421,360
Latest member
Rhodia

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back