locked
Excel 2003 function doesn't work in Excel 2010 RRS feed

  • Question

  • This function works perfectly in Excel 2003. In 2010 it shows a #name? error. Here's the formula - =TRIM(extractelement(A2,2,","))

    Function ExtractElement(Txt, n, Separator) As String
    '   Returns the nth element of a text string, where the
    '   elements
    '    are separated by a specified separator character

        Dim Txt1 As String, temperament As String
        Dim ElementCount As Integer, i As Integer
        Dim TempElement As Variant
       
        Txt1 = Txt
    '   If space separator, remove excess spaces
        If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
       
    '   Add a separator to the end of the string
        If Right(Txt1, Len(Txt1)) <> Separator Then _
            Txt1 = Txt1 & Separator
       
    '   Initialize
        ElementCount = 0
        TempElement = ""
       
    '   Extract each element
        For i = 1 To Len(Txt1)
            If Mid(Txt1, i, 1) = Separator Then
                ElementCount = ElementCount + 1
                If ElementCount = n Then
    '               Found it, so exit
                    ExtractElement = TempElement
                    Exit Function
                Else
                    TempElement = ""
                End If
            Else
                TempElement = TempElement & Mid(Txt1, i, 1)
            End If
        Next i
        ExtractElement = ""
    End Function

    Thursday, May 31, 2012 4:34 PM

Answers

  • The function works fine for me in Excel 2010.

    Have you created the function in a standard module in the same workbook where you use it? If so, it should work. The function should *not* be in a worksheet module or in the ThisWorkbook module.

    If you have created the function in your personal macro workbook Personal.xlsb, you should use

    =TRIM(Personal.xlsb!ExtractElement(A2, 2, ","))


    Regards, Hans Vogelaar

    • Marked as answer by Jaynet Zhang Monday, June 4, 2012 1:59 AM
    Thursday, May 31, 2012 4:39 PM
  • For a formula alternative, try:

    =TRIM(MID(SUBSTITUTE(A1,Separator,REPT(" ",999)),n*999-998,999))

    HTH! David Hager

    Excel FMVP

    • Marked as answer by Jaynet Zhang Monday, June 4, 2012 2:00 AM
    Thursday, May 31, 2012 9:07 PM

All replies

  • The function works fine for me in Excel 2010.

    Have you created the function in a standard module in the same workbook where you use it? If so, it should work. The function should *not* be in a worksheet module or in the ThisWorkbook module.

    If you have created the function in your personal macro workbook Personal.xlsb, you should use

    =TRIM(Personal.xlsb!ExtractElement(A2, 2, ","))


    Regards, Hans Vogelaar

    • Marked as answer by Jaynet Zhang Monday, June 4, 2012 1:59 AM
    Thursday, May 31, 2012 4:39 PM
  • For a formula alternative, try:

    =TRIM(MID(SUBSTITUTE(A1,Separator,REPT(" ",999)),n*999-998,999))

    HTH! David Hager

    Excel FMVP

    • Marked as answer by Jaynet Zhang Monday, June 4, 2012 2:00 AM
    Thursday, May 31, 2012 9:07 PM