I'm trying to truncate Excel columns to certain length in VBA.
\n and \r
print len( replace(replace(sheet1.Cells(1,1).value, chr(10), "-" ), chr(13), "#"))
varchar(50) means 50 bytes
stringBytes = strCellValue.getBytes("UTF-8");
stringLength = stringBytes.length;
strCellValue = new String( stringBytes, 0, maxCellWidth, "UTF-8");
I've just tested copy/paste from Notepad++ and verified that it's not copying the carriage return.
If you check on your cell like:
You'll see the result of
0 indicating that the
Char(10) & Char(13)) simply doesn't 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 isn't 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...