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

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
Joined
Mar 4, 2005
Messages
2,071
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
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 1, 2003
Messages
17,832
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
    .DisplayHeadings = False
    .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 ? :LOL:

kind regards,
Erik
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top