title: VBA get length of string with white space characters like new line and carriage return
body: <p>I'm trying to truncate Excel columns to certain length in VBA.</p>
  
  <p>However <code>LEN</code> function doesn't seem to count <code>\n and \r</code> as individual characters.</p>
  
  <p>Is there any other function that can give exact length of string ?</p>
  
  <p>For example I'm trying to get length of following string in a cell. (<code>a\r\nb\r\nc\r\nd</code>)</p>
  
  <pre><code>a
  b
  c
  d
  </code></pre>
  
  <p>Code to get length.</p>
  
  <pre><code>print len( replace(replace(sheet1.Cells(1,1).value, chr(10), "-" ), chr(13), "#"))
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>7
  </code></pre>
  
  <p>Actual length of the string is <code>10</code>.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I finally figured it out. It wasn't related to the white space characters but some other special Unicode characters.</p>
  
  <p>VBA <code>LEN</code> function and Java <code>String.length()</code> 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 (<code>varchar(50) means 50 bytes</code>) it wasn't allowing the string to be inserted even after truncation.</p>
  
  <p>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.</p>
  
  <pre><code>stringBytes = strCellValue.getBytes("UTF-8");
  stringLength = stringBytes.length;
  
  strCellValue = new String( stringBytes, 0, maxCellWidth, "UTF-8");
  cell.setCellValue(strCellValue);
  </code></pre>
  
  <p>Thanks to David Zemens and P57 for the clues.</p>