title: Modifying excel cell values based on their length with a UDF
body: <p>I was asked to modify some phone number values in an excel column and I wanted to use the opportunity to make a UDF for the first time.</p>
  
  <p>Currently I have a list of phone numbers that vary in size. Some numbers are complete and some are incomplete (I.E. lengths range from 7 digits to 11). The target length for every number is 10 digits. If the number is greater than 10 digits I trim off the first number, if it is less i would append "0" to the beginning of the string until it was 10 digits long.</p>
  
  <p>Im not entirely sure what I am doing wrong so I was hoping someone could point me in the right direction. At the moment I am getting a "#VALUE" in my function cell. I am targeting a cell that is formatted as a "text" cell. Any advice is appreciated! Here is my function...</p>
  
  <pre><code>Public Function phone_number_trim(phone_number As String)
  
  Dim phone_number_length As Integer
  Dim s_phone_number As String
  
  
  phone_number_length = phone_number.Len
  s_phone_number = phone_number
  
  If phone_number_length &gt; 10 Then s_phone_number = Mid(s_phone_number, 2, 10)
  
  If phone_number_length &lt; 10 Then
      For i = 0 To phone_number_length
      s_phone_number = "0" + s_phone_number
      Next
  End If
  
  phone_number = s_phone_number
  
  End Function
  </code></pre>
  
  <p>Here is what my editor looks like just in case...
  <a href="https://i.stack.imgur.com/mki6z.png" rel="nofollow noreferrer">Editor</a></p>