TEST how many caracters can appear in one cell

[erik.van.geit]

Do you know how many caracters can appear in one cell ?
With this macro you can do some testing ...

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

 

[PaddyD]

=SUBSTITUTE(A1,".","."&CHAR(10))

 

[JamesKM]

Quick, Easy, Effective

Thanks