EXCEL 2003:How to pick a specific subset of data within field array 1

[JAMER101]

Help ?

I have a column within Excel (2003) which contains several elements of data, is there a way without using text to columns that I can pick the Nth element of data. The data is delimited.

eg
A1 contains "Frank,Betty,Alfred,Stan,Peter"

in B1 I want to display the 3 element of data ie "Alfred".

If there is a solution, can the delimiter also be specified ie if the data was
"Frank|Betty|Alfred|Stan|Peter"

Here's hoping...

 

[GlennUK]

Can I ask why Text To Columns cannot be used? That would make things very simple indeed.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

For a not so simple answer, how about this array formula? This is entered using Ctrl-Shift-Enter:

=MID(A1,SMALL(IF(MID(Delimiter&A1&Delimiter,ROW($1:$256),1)=Delimiter,ROW($1:$256),999),NthChoice),SMALL(IF(MID(Delimiter&A1&Delimiter,ROW($1:$256),1)=Delimiter,ROW($1:$256),999),NthChoice+1)-SMALL(IF(MID(Delimiter&A1&Delimiter,ROW($1:$256),1)=Delimiter,ROW($1:$256),999),NthChoice)-1)

Replace Delimiter with whatever delimiter you require, and NthChoice with whatever nth word number you want.


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

Can you use vba? It's quite simple with UDF:

Function PickNthItem(InputString As String, Delimiter As String, N As Integer) As String
On Error Resume Next
PickNthItem = Split(InputString, Delimiter)(N - 1)
End Function

annd in worksheet:
=PickNthItem(A1,",",3)

combo

 

[JAMER101]

GlennUK,

thanks for that it works a treat, don't mind complicated just as long as it works.

(With regards the ther posts, I don't know VBA and didn't want to use text to columns as wanted to create something with as few steps as possible for the end user to work with.

Thanks to you all.

Jamer101

 

[GlennUK]

Hi Jamer101,

my pleasure. Glad to have helped

Cheers, Glenn.

Beauty is in the eye of the beerholder.