Convert Selection to Number Format

[wbsgn12]

I have a data column (Column A) containing a mixture of (1) text (2) numbers formatted as text. I would like to extract only the data in Column A containing numbers and move the extracted array into Column F. Any thoughts?

[Kaper]

Try such code:

Please Login or Register  to view this content.

Note that output range is formatted as text before writing there data. Otherwise (with genral format), excel would write content of outarr as numbers (so 110 instead of 0110, etc.)

[Vraag en antwoord]

Without duplicate values

Please Login or Register  to view this content.

[Marc L]

As a VBA beginner starter without any loop but removing the duplicates :

Sub Demo1() 
    Dim V 
        V = Filter(Evaluate(Replace("TRANSPOSE(IF(ISERR(#*1),FALSE,#))", "#", [A1].CurrentRegion.Address)), False, False) 
    If UBound(V) > -1 Then 
        With [F2].Resize(UBound(V) + 1) 
            .NumberFormat = "@" 
            .Value2 = Application.Transpose(V) 
            .RemoveDuplicates [{1}], xlNo 
        End With 
    End If 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Marc L]

As an obvious easy VBA beginner starter :

Sub Demo2() 
        [D2].Formula = "=NOT(ISERR(A2*1))" 
    With ActiveSheet.UsedRange.Columns 
        .Item(6).Clear 
        .Item(1).AdvancedFilter xlFilterCopy, [D1:D2], [F1], True 
    End With 
        [D2].Clear 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄