cut and paste partial text from cell

[npacker]

Hi everyone, quick question that I believe would be pretty simple, but I'm just not sure how to do it. As we all know, text wrap will only disply 1024 characters in a cell. How could I write a macro, that runs every time more than say, 1000 characters is placed in a cell, selects all the characters in excess of 1000, cuts them, and pastes them into the next cell down?
Thanks,
Nathan

PS: If you want to go the extra mile, figure this out: Rather than just cutting out any characters over 1000, find the 1000-character point and round to the nearest word, the cut from that point, and paste.

 

[npacker]

I've now learned how to at least count the number characters in a cell, using the following code:

Sub CountMe()
Dim mycount As String
Dim StringLength As Integer
MyString = Range("A1").Text
StringLength = Len(MyString)
MsgBox StringLength
End Sub

So now that I can get a variable to store the number of characters in a cell, can I somehow use the "Right" function to have it count a number of characters from the right, cut them, and paste them somewhere else?
Thanks,
Nate

 

[npacker]

I know it sounds like I'm talking to myself, but I'm hoping something I say will trigger some help. I just realized something stupdily funny. My whole problem here is counting characters over 1024. So you see my code above? I just ran it with about 2000 characters in "A1", and my message box only returned 1024! So now I can't even use the code I have, cause the "Len" function won't even see anything above 1024!
Help!
Nate

 

[Tazguy37]

The LEN() function in VBA won't return anything over 1,024, but Excel's LEN() funtion will. So, instead, you could do something like this:

Sub CountMe()

    Dim mycount As String
    Dim StringLength As Integer
    Dim MyString As String
    
    MyString = Range("A1").Text
    Range("B1").Formula = "=len(A1)"
    
End Sub

Hope that helps!

 

[npacker]

Thats great, now I can grab the number in B1 to usein my code, but do you have any ideas as to how I can use that to chop off any characters in exces of 1024, and paste them in another cell down or at least get them to manipulate them? This is the code I can use to get the number of characters in excess of 1024, but I just don't know what to do next:

Sub CountMe()

    Dim mycount As String
    Dim StringLength As Integer
    Dim MyString As String
    Dim Excess As Integer

    MyString = Range("A1").Text
    Range("B1").Formula = "=len(A1)"
    Excess = Range("B1").Value - 1024
    
End Sub

Thanks!
Nate

 

[Tazguy37]

You can use the Right() and Left() string functions to separate the initial string into 2 pieces. Have a look at the code I created below. It will separate a string (from A1) into substrings of a certain length. If you want divisions to only occur at spaces, more checking would be necessary, but this should get you started. Please post back with any questions on the code.

Sub divideString()
Dim myStr, smStr As String
Dim iRow, lenRem
Dim numChars As Double

myStr = Range("A1").Text
iRow = 3                                            'start output in row 3
numChars = 12                                       'number of characters for each cell

Do
    lenRem = Min2(Len(myStr), numChars)             'find remaining length
    smStr = Left(myStr, lenRem)                     'left portion of remaining string
    myStr = Right(myStr, Len(myStr) - lenRem)       'right ""     "" ""        ""
    Range("A" & iRow) = smStr                       'write numChars characters to next cell down in A
    iRow = iRow + 1
Loop While lenRem > 0                               'until the end

End Sub

Function Min2(a As Double, b As Double) As Double
    If a < b Then
        Min2 = a
    Else
        Min2 = b
    End If
End Function

Hope that helps!