Sometimes you need to know what a function name is in the local language.
E.g. when adding FormatConditions with VBA code the formulas in the FormatCondition must be added using formula names and list separators in the localized language.

The function below can be used to translate an English function name into the local function name or from the local function name to an English function name.

Function TranslateFunctionName(strFormula As String, Optional blnFormulaIsDefaultEnglish As Boolean = True, Optional lngReferenceStyle As XlReferenceStyle = xlA1) As String
' updated 2021-09-26 by OPE
' returns the translated name for the first function in strFormula, either in English or the local language
' the function name must be located immediately after the = char in the formula
' strFormula must be a valid formula written in default English format (blnFormulaIsDefaultEnglish = True) or the local language (blnFormulaIsDefaultEnglish = False)
' if blnFormulaIsDefaultEnglish = True then the result will be the local formula name
' if blnFormulaIsDefaultEnglish = False then the result will be the English formula name
' set lngReferenceStyle to alA1 or xlR1C1 depending on the reference style used in strFormula
' NOTE! this function translates around 100+ functions per second depending on the computer hardware, avoid using this function multiple times, e.g. in a loop with more than 100 items
' example: strFunction = TranslateFunctionName("=if(true,false,true)") ' translate from english to local
' example: strFunction = TranslateFunctionName("=mid(""abcdef"",3,2)") ' translate from english to local
' example: strFunction = TranslateFunctionName("=sum(A1:A10)") ' translate from english to local
' example: strFunction = TranslateFunctionName("=true") ' translate from english to local
' example: strFunction = TranslateFunctionName("=hvis(sann;usann;sann)", False) ' translate from local to english
' example: strFunction = TranslateFunctionName("=deltekst(""abcdef"";3;2)", False) ' translate from local to english
' example: strFunction = TranslateFunctionName("=summer(A1:A10)", False) ' translate from local to english
' example: strFunction = TranslateFunctionName("=sann", False) ' translate from local to english
    strFormula = Trim(strFormula)
    If Len(strFormula) = 0 Then Exit Function

    Const cstrName As String = "TempTranslateFunction"
    Dim strNameTemp As String, blnSaved As Boolean, objName As Name, strResult As String
    If Left(strFormula, 1) <> "=" Then strFormula = "=" & strFormula
    With ThisWorkbook
        blnSaved = .Saved
        On Error Resume Next
        .Names(cstrName).Delete
        If blnFormulaIsDefaultEnglish Then
            If lngReferenceStyle = xlA1 Then
                Set objName = .Names.Add(cstrName, strFormula, False)
            Else
                Set objName = .Names.Add(cstrName, , False, , , , , , , strFormula)
            End If
            strResult = objName.RefersToLocal
        Else ' formula in the local language
            If lngReferenceStyle = xlA1 Then
                Set objName = .Names.Add(cstrName, , False, , , , , strFormula)
            Else
                Set objName = .Names.Add(cstrName, , False, , , , , , , , strFormula)
            End If
            strResult = objName.RefersTo
        End If
        If Not objName Is Nothing Then objName.Delete
        On Error GoTo 0
        .Saved = blnSaved
    End With
    If Len(strResult) = 0 Then Exit Function
    
    Dim i As Long
    i = InStr(1, strResult, "(", vbBinaryCompare)
    If i > 0 Then strResult = Left$(strResult, i - 1)
    If Left$(strResult, 1) = "=" Then strResult = Mid$(strResult, 2)
    TranslateFunctionName = strResult
End Function