title: How can i can truncate upto certain characters in Excel VBA?
body: <p>I have an excel sheet with some rows of descriptions in a single column, what I am aiming is to get a vba that would go though all those rows of descriptions and truncate it upto certain character limit for example 30 characters and if the truncation stops at 30 character in the middle of the word then I want the complete word(could extend beyond 30 characters in this case).</p>
  
  <p>I tried to do this with the VBA code below, but I am not able to get what I am looking for.</p>
  
  <pre><code>Function foo(r As Range)
  Dim sentence As Variant
  Dim w As Integer
  Dim ret As String
  
  ' assign this cell's value to an array called "sentence"
  sentence = Split(r.Value, " ")
  
  ' iterate each word in the sentence
  For w = LBound(sentence) To UBound(sentence)
      ' trim to 6 characters:
      sentence(w) = Left(sentence(w), 6)
  Next
  
  ' Join the array back to a string/sentence
  ret = Join(sentence, " ")
  
  'Make sure the sentence is max 20 chars:
  ret = Left(ret, 20)
  
  
  'return the value to your function expression:
  foo = ret
  End Function
  </code></pre>
  
  <p>I expect the code to go through all the rows of a specific column and truncate it upto 30 characters and if the truncation stops in the middle of the word, then it should keep that word.</p>