title: How do I display different text than the cell text in a cell?
body: <p>I have an Excel sheet where I have two cells on one side, one of which includes a two-digit number with a leading zero formatted as text and the other one includes an explanation. On the other side I have another empty cell. I want the explanation text to be displayed in that empty cell but the cell should have the two-digit number that's formatted as text as its value internally. I got it to work with a number as the value, but not with the number that's formatted as text.</p>
  
  <p>I've tried the <code>.Text</code> tag but it always throws a syntax error. My suspicion is that that's because of the <code>.NumberFormat</code> tag and how it's formatted. [Disclaimer: I didn't write this code myself but I have to fix it.]</p>
  
  <pre><code>Sub UserDefinedFormatting()
  
  ' Delete old output:
  
  Range("G6:G500").ClearContents
  Range("G6:G500").NumberFormat = "Text"
  
  ' Loop for new output:
  
  For n = 5 To 200
  
      If Cells(n, 3).Value &lt;&gt; 0 Then
  
      Value_actual = Cells(n, 3).Value
      Value_shown = Cells(n, 4).Value
  
      Cells(n, 7).Value = Value_shown
      Cells(n, 7).NumberFormat = "[=" &amp; Value_actual &amp; "]""" &amp; Value_shown &amp; """;General"
      Cells(n, 7).Value = Value_actual
  
      End If
  
  Next n
  
  End Sub
  </code></pre>
  
  <p>Desired outcome:</p>
  
  <pre><code>| I1 |  I2  |      Output     |
  | 05 | Test | Test[Value: 05] |
  </code></pre>
  
  <p>Actual outcome:</p>
  
  <pre><code>| I1 |  I2  |     Output     |
  | 05 | Test | Test[Value: 5] |
  </code></pre>
  
  <p>Somehow it always takes away the leading zero which is essential.</p>