title: Split not working excel VBA
body: <p>Input in column <code>H</code> from <code>Row 2</code></p>
  <pre><code>column H
  90 million +
  550 million +
  150 million +
  2.1 billion +
  110 million +
  .
  .
  .
  </code></pre>
  <p>Expected Output in Column <code>J</code> from <code>Row 2</code></p>
  <pre><code>    90 
      550 
      150 
      2.1 
      110 
        .
        .
        .
  </code></pre>
  <p>VBA Code:</p>
  <pre><code>Option Explicit
  Sub ConvertRange()
  Dim str As String
  Dim Cet
  Dim i, j, l As Long
  Dim FinalRow As Long
  Dim wk As Worksheet
  Application.ScreenUpdating = False
  
  
  Set wk = Sheets(&quot;Sheet1&quot;)
  
  FinalRow = wk.Range(&quot;H900000&quot;).End(xlUp).Row
  
  For i = 2 To FinalRow
  str = Cells(i, &quot;H&quot;).Value
  str = Replace(str, &quot; &quot;, &quot;+&quot;)
  str = Replace(str, &quot;&quot;, &quot;+&quot;)
  str = Replace(str, &quot; &quot;, &quot;+&quot;)
  Cet = Split(str, &quot;+&quot;)
  
  Range(&quot;J&quot; &amp; i) = Cet(LBound(Cet))
  
  
  Next i
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  <p>But I am getting wrong Output in Column <code>J</code> As follows</p>
  <pre><code>90 million 
  550 million 
  150 million 
  2.1 billion 
  110 million 
  .
  .
  .
  </code></pre>
  <p>I believe either of these commands would have replaced the white spaces with the <code>+</code> sign</p>
  <pre><code>str = Replace(str, &quot;&quot;, &quot;+&quot;)
  str = Replace(str, &quot; &quot;, &quot;+&quot;)
  </code></pre>
  <p>And following line would have an Array wherein LBound value id Cet should be only numbers</p>
  <pre><code>Cet = Split(str, &quot;+&quot;)
  </code></pre>
  <p>Where am I wrong ?</p>
  <blockquote>
  <p>Basically what I want is Cet(0) should be the long or integer number and Cet(1) should be the text billion or million depending on the text in col H for every loop so that I can process this further</p>
  <p>So if Column H Row 2 value is 90 million Cet(0)=90 and Cet(1)=million</p>
  </blockquote>