How to call Excel formula using VBA-CodePudding

I am using below mentioned formula to filter out the unwanted text from a Cell. I would like to use this formula via VBA, i have tried some method that includes Sub Formula_Property and Recording Macro but not successful.

The formula is mentioned below:

=LEFT(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3 & "1234567890"))-1)

The Main data is available in Sheet1 ColumnA2:A5000 and I would like the filtered text (Result) in Sheet3 column B2:B5000.

Is there anyway i can automate this using VBA, that would be helpful for me much.

Thank you

CodePudding user response：

This macro will apply that formula to the range you wish.

Sub Filter_Text_Formula()
Sheets("Sheet3").Range("B2:B5000").FormulaR1C1 = "=LEFT(Sheet1!RC[-1],MIN(FIND({1,2,3,4,5,6,7,8,9,0},Sheet1!RC[-1] & ""1234567890""))-1)"
End Sub

CodePudding user response：

Parse Left of First Digit

Sub ParseLeftOfDigit()
    
    Dim Data(): Data = Sheet1.Range("A2:A5000").Value
    
    Dim r As Long, n As Long, rStr As String
    
    For r = 1 To UBound(Data, 1)
        rStr = CStr(Data(r, 1))
        For n = 1 To Len(rStr)
            If Mid(rStr, n, 1) Like "#" Then
                Data(r, 1) = Mid(rStr, 1, n - 1)
                Exit For
            End If
        Next n
    Next r
    
    Sheet3.Range("B2:B5000").Value = Data

End Sub