TEST how many caracters can appear in one cell

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Do you know how many caracters can appear in one cell ?
With this macro you can do some testing ...

Code:
Option Explicit

Sub count_caracters_in_cell()
Dim cell As Range
Dim nr As Integer
Dim x As Integer
Dim temp As String

Set cell = ThisWorkbook.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 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
cell.EntireColumn.ColumnWidth = 120
cell.RowHeight = 350
cell.WrapText = True
cell.Font.Size = 4 'change this to test
For x = 1 To (nr - 20) / 10
temp = temp & "oooooooooo"
Next x
temp = "STARTooooo" & Left(temp, Len(temp)) & "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
cell.Offset(1, 0).FormulaR1C1 = "=LEFT(R[-1]C,10) & "" "" & RIGHT(R[-1]C,10)"
cell.Select
With ActiveWindow
.DisplayHeadings = False
.Zoom = True
cell.Offset(1, 0).Select
.ScrollRow = 1
.ScrollColumn = 1
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "number of caracters put in the cell: " & " " & Len(temp) & Chr(10) & _
"number of caracters counted in the cell: " & " " & Len(cell) & Chr(10) & _
"START & END at the beginning and 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"
End Sub
kind regards to everybody,
Erik
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Erik,

If you type in "Specifications" in help, it'll tell you this kind of thing. For a cell, max length = 32,767, but only 1024 will display in the cell. All 32K display in the formula bar.

Also if you copy a worksheet, only the first 255 characters of a cell get copied by default, though you can go back and copy and paste long cells one-by-one (programmatically or interactively).
 
Upvote 0
Thanks for response,

You can display more then 1024 caracters in a cell. This macro proves it on my screen (XP). The max I got is 1222.

I'll take a look in the help now.
Just being curious...

regards,
Erik
 
Upvote 0
Try this:

Enter the formula
="Z"&REPT(REPT("abcd ",50)&CHAR(10),29)&"Z"

If you just do that, the total viewable characters in the cell is about 1031.

If you align for wrapped text you can now see 7250 or so charcaters.

The effect of Alt+Enter allows for a much greater view of characters in a cell, when the zoom, column width and row height are adjusted.
 
Upvote 0
Tom,
very nice
what I was doing in some hundreds of carters of code, you do in a single formula and some settings !

regards,
Erik
 
Upvote 0
Well, that's what I get for quoting Excel's help! By using Tom's example + small fonts, max width, max height and zoom, I can see 10,456. Legible but definitely not for folks that need bifocals...
 
Upvote 0
haze2000,

What a beatiful entry you make on this forum.
You're the first, as far as I know who comes in with thanks for answers at the end of a thread without posting a question :p

WELCOME TO THE BOARD !!!

kind regards,
Erik
(y)
 
Upvote 0
Following on from this thread - is it possible to write a function or formula that finds the "." (full stops) for example in a cell (A1) and after each returns an Alt Enter so that test over 1024 characters is displayed in full?

I was thinking something along the lines of find "." insert Alt Enter, find next "." insert Alt Enter, etc.

Any helps is appreciated.

James
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,771
Members
448,297
Latest member
cocolasticot50

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
Back
Top