# Excel 2003 function doesn't work in Excel 2010

• ### 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

• 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 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 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 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 Monday, June 4, 2012 2:00 AM
Thursday, May 31, 2012 9:07 PM