Excel Convert Formula to User Function-CodePudding

I built a formula the spits off an email address with number from another group of numbers. For example I have thousands of the following types of strings:

[email protected]
Result after Formula: [email protected]

My Excel formula which works perfectly is: =RIGHT($A$6,LEN($A$6) LEN(LEFT(A$6$, (MATCH(TRUE,ISERROR(VALUE(MID($A$6,ROW(INDIRECT("1:"&LEN($A$6))),1))),0))-1))). With special notes on how I handled VALUE (changed it to VAL) and the INDERECT function ( is bracketed its calc [ xxx ], Here is the broken user function that I think should work, however; it just does not work.

  Function Test(Cell_Addrs As Range)
    
     NewString = Right(Cell_Addrs, Len(Cell_Addrs) - Len(Left(Cell_Addrs, _ 
    (Application.WorksheetFunction.Match _
    (True, IsError(Val(Mid(Add_Rss, Application.WorksheetFunction.Row _
    ([INDIRECT("1:" & Len(Cell_Addrs))]), 1))), 0)) _ 
    - 1)))

    Test = NewString

End Function

CodePudding user response：

Use the Val() function to get the preceding number and just replace it with an empty string.

The Val function stops reading the string at the first character that it can't recognize as part of a number.

Public Function ToEmail(rng As Range) As String
    ToEmail = Replace(rng.Value, Val(rng.Value), vbNullString)
End Function