1024 character limit help

[MontySharpei]

Hello,

I need to be able to put more than 1024 characters into a cell but I know xl won't allow it. So my question is this...

Is their a macro that could be automatically run to say when for example a hundred words is typed into a cell a new line below the cell is added and the 101 - 200 words following the first is automatically placed into this cell and this is repeated for words 201 - 300 etc and so on... ??

[marshybid]

Hello,

I need to be able to put more than 1024 characters into a cell but I know xl won't allow it. So my question is this...

Is their a macro that could be automatically run to say when for example a hundred words is typed into a cell a new line below the cell is added and the 101 - 200 words following the first is automatically placed into this cell and this is repeated for words 201 - 300 etc and so on... ??
Hi MontySharpei,

I found the following formula a long time ago which enables you to count the number of words in a cell



=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

This is counting words in cell A1. If cell A1 contains hello have a nice day, place this formula in any other cell and it will return a value of 5

You can also use the following formula to count words in a range of cells


=SUM(IF(LEN(TRIM(A1:D7))=0,0,LEN(TRIM(A1:D7))-LEN(SUBSTITUTE(A1:D7," ",""))+1))

This formula must be entered as an array formula ( ctrl-shift-enter)

I'm sure that someone else on this site can help you to capture the value returned by the word count in a cell formula (the first one) and use that to select the first 100 words then move 200 - 300 to the next line.

I'm afraid that is beyond my skills.

Hope this may help though

Marshybid

[Not So Smart]

'with the cell with the text selected
textnum = Selection.Characters.Count
If textnum > 850 Then
MsgBox "You have exceeded the 850 character limit. Please break the comment into two parts."
End
Else
End If


'for a text box
z = UserForm2.TextBox1
L = Len(z)
MsgBox L


'here is some old code I wrote, I am not exactly sure what it is but it is related to your topic:
'Paste phrase to sheet

d = ListBox1.ListIndex
If d = -1 Then
MsgBox "Select Keyword to paste."
Unload Me
DisplayListBox
Else
End If

z = ListBox1.ListIndex + 2

q = ActiveCell.Value

If Len(q & " " & Sheets("DropDowns").Range("B" & z).Value) > 1024 Then

If ActiveCell.Row = 140 Then
ActiveSheet.Unprotect
If Rows("141:141").Hidden = True Then Rows("141:141").Hidden = False
Range("B141").Select
ActiveCell.Value = Sheets("DropDowns").Range("B" & z).Value
Else
MsgBox "You have exceeded the 1024 character limit. Please reduce the size of your comment (spaces count)."
ActiveCell.Value = ""
End
End If
Else
ActiveCell.Value = q & " " & Sheets("DropDowns").Range("B" & z).Value
End If

[mdmackillop]

Have you seen this? (http://www.vbaexpress.com/kb/getarticle.php?kb_id=421)

[marshybid]

Have you seen this? (http://www.vbaexpress.com/kb/getarticle.php?kb_id=421)

Brilliant, something I will also be able to use on a number of sheets.

Marshybid :hi: