EXCEL vba - extract numbers from cell and paste into two different columns?
<p>I have a spreadsheet with a load of random text and numbers in column A like so:</p>

<pre><code>Column A

    Row 1 = 471806121601 5205569 - 0007 Standard White Toilet Tissue 27 
    Row 2 = 471814121601 5206177 - 0014 Premium White Toilet Tissue 6 
    Row 3 = 471814121601 5206178 - 0007 Premium White Toilet Tissue 27
    Row 4 = 471806121601 5206180 - 0014 Premium Kitchen Towel 2x75l 6 
</code></pre>

<p>I have about 2000 lines in total. In each cell, is a Purchase order number (12 digits) and an item number next to it (7 digits).</p>

<p>I am trying to extract the po number and put it into column B and extract the item number and put it into column C</p>

<pre><code>Column B        Column C
471806121601    5205569 
471814121601    5206177 
471814121601    5206178 
471806121601    5206180 
</code></pre>

<p>Here is my code:</p>

<pre><code>Option Explicit

Sub main()
Dim cell As Range
Dim arr As Variant, arrElem As Variant

With Worksheets("Orders") '&lt;--| change "Strings" to your actual worksheet name
    For Each cell In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
        arr = Split(Replace(cell.Value, "  ", " "), " ") '&lt;--| change "A"'s to your actual relevant column index
        For Each arrElem In arr
          If IsNumeric(arrElem) Then
            If Len(arrElem) = 12 Then cell.Offset(0, 1).Value = arrElem
          End If
        Next arrElem
    Next cell
End With

Dim cell2 As Range
Dim arr2 As Variant, arrElem2 As Variant

With Worksheets("Orders") '&lt;--| change "Strings" to your actual worksheet name
    For Each cell2 In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
        arr2 = Split(Replace(cell2.Value, "  ", " "), " ") '&lt;--| change "A"'s to your actual relevant column index
        For Each arrElem2 In arr2
          If IsNumeric(arrElem2) Then
            If Len(arrElem2) = 7 Then cell2.Offset(0, 3).Value = arrElem2
          End If
        Next arrElem2
    Next cell2
End With

End Sub
</code></pre>

<p>This code does work. However it takes absolutely ages and only does one line at a time...Slowly.</p>

<p>Is there a quicker way of doing this? Thanks</p>