java – VBA get length of string with white space characters like new line and carriage return

java – VBA get length of string with white space characters like new line and carriage return

Ive just tested copy/paste from Notepad++ and verified that its not copying the carriage return.

If you check on your cell like:

Instr(Sheet1.Cells(1,1).Value, vbCrLF)

Youll see the result of 0 indicating that the vbCrLF (Char(10) & Char(13)) simply doesnt exist in the cell. The Clipboard must be altering the text stream somehow.

So the actual length of the string in Excel is only 7.

To fix this, convert the Line Feeds to a vbCr & vbLf:

Debug.Print Len(Replace(sheet1.Cells(1,1).value, vbLF, vbCR & vbLF))

NOTE: This probably isnt a one-size-fits-all approach, it assumes that all Line Feeds should be coerced to a Carriage Return + Line Feed. Your mileag may vary.

Advice is not to copy/paste from Notepad++, and consume text files directly as a TextStream object to your VBA…

Im still curious why youd be seeing a character count of 10 for your example if reading the Excel value from Java.

This code gives some further insight into how an Excel range copies back to clipboard. It really looks like after pasting into the cell the carriage return is gone for good, but also that, after copying, the actual clipboard text includes quotations and has nr appended, but no trace of the original r characters.

 Sub CellStringCheck()

    Copy cell to clipboard
    Range(A1).Copy

    Get clipboard contents directly
    Dim obj As MSForms.DataObject
    Set obj = New MSForms.DataObject
    obj.GetFromClipboard

    Convert to string and set object to nothing
    Dim s As String
    s = obj.GetText
    Set obj = Nothing

    Print Information
    Debug.Print ___________________
    Debug.Print String: 
    Debug.Print s
    Debug.Print Len =  & Len(s)
    Debug.Print Characters:
    On Error Resume Next
    For i = 1 To Len(s)
        Debug.Print AscW(Mid(s, i, 1))
    Next

End Sub

If I paste AnrA into range A1, this code produces the following:

    ___________________
String:
A
A 

Len = 7
Characters:
34
65
10
65
34
13
10

If you paste this clipboard content back into NP++ you get quotations and all, but NP++ automatically converts the lone n back into nr, apparently on its own because thats not part of the clipboard content.

I wondered if this had to do with the clipboard format being VbCFText, but if VBA handles the clipboard pasting, then theres no problem. No carriage returns are removed as shown by using this code:

    Sub CellStringCheck()

    Set clipboard contents
    Dim obj As MSForms.DataObject
    Set obj = New MSForms.DataObject
    obj.SetText vbLf & vbCr
    obj.PutInClipboard

    Copy cell to clipboard
    obj.GetFromClipboard
    Range(A1) = obj.GetText
    Range(A1).Copy

    Get clipboard contents directly
    obj.GetFromClipboard

    Convert to string and set object to nothing
    Dim s As String
    s = obj.GetText


    Print Information
    Debug.Print ___________________
    Debug.Print obj.GetFormat(1)
    Debug.Print String: 
    Debug.Print s
    Debug.Print Len =  & Len(s)
    Debug.Print Characters:
    On Error Resume Next
    For i = 1 To Len(s)
        Debug.Print AscW(Mid(s, i, 1))
    Next

    Set obj = Nothing
End Sub

So, all this to say, Im still not sure why youd be finding carriage returns at all if reading the cell value directly from Java, but if youre going through the clipboard, then you may be picking up the appended carriage return. Apparently, Excel and NP++ do their own string handling when pasted to. Excel removes carriage returns, while NP++ converts n to nr. If you go directly through the clipboard this doesnt happen, so I suspect it has to do with application level event handling. I havent tested directly reading or writing to values from .Net or Java, but my guess is that directly accessing values would avoid this problem. Another work around would be to use Environment.NewLine or force nr for any instance of n.

java – VBA get length of string with white space characters like new line and carriage return

I doubt this is the best answer; in fact, Im quite sure of it, but one rather convoluted approach would be to prior to using Len function, do a replace on the carriage returns and new lines. Something like

for i = 1 to 10
    cells(i,1) = Replace(Cells(i,1), Char(10), -) newline Chr(10)
    cells(i,1) = Replace(cells(i,1), Char(13), -) carriage return Chr(13)
next i

Then you would have a dash where the newlines and carriage returns were and could apply Len to the cell in question to get the full length.

Leave a Reply

Your email address will not be published. Required fields are marked *