11thdimension 11thdimension - 5 months ago 22
Java Question

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

I'm trying to truncate Excel columns to certain length in VBA.

However

LEN
function doesn't seem to count
\n and \r
as individual characters.

Is there any other function that can give exact length of string ?

For example I'm trying to get length of following string in a cell. (
a\r\nb\r\nc\r\nd
)

a
b
c
d


Code to get length.

print len( replace(replace(sheet1.Cells(1,1).value, chr(10), "-" ), chr(13), "#"))


Output:

7


Actual length of the string is
10
.

Edit

I finally figured it out. It wasn't related to the white space characters but some other special Unicode characters.

VBA
LEN
function and Java
String.length()
methods do not give the size of the string but the count of characters. Since Oracle was using the byte length as the actual size of the column (
varchar(50) means 50 bytes
) it wasn't allowing the string to be inserted even after truncation.

I finally used following code to truncate the string at Java side, as I wasn't able to find any method to do so in VBA.

stringBytes = strCellValue.getBytes("UTF-8");
stringLength = stringBytes.length;

strCellValue = new String( stringBytes, 0, maxCellWidth, "UTF-8");
cell.setCellValue(strCellValue);


Thanks to David Zemens and P57 for the clues.

Answer

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:

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

You'll see the result of 0 indicating that the vbCrLF (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...