Subfield with consecutive delimiters - a bug?

[#1]

If I have a string with the occasional multiple delimiters between data, Subfield extracts the delimiter and not the expected data.

Example:

=subfield('abcdef    MyData zzzz', ' ' , 2)

I expect the above to return 'MyData', but it returns ' ' (a space)

I could use the following very ugly statement, but I'm not thrilled about doing so, especially since this needs to be done during a load.

=Subfield(TRIM( MID('abcdef    MyData zzzz',INDEX('abcdef    MyData zzzz',' ',1)) ),' ',1)

Based on my experience with many other languages, I expected the subfield to eat the extra consecutive  delimiters, but that is not happening.

Q1) is this the expected Subfield behavior or a bug?

Q2) Does anyone have a better method than my above expression?

TIA,

John

QV 11.20, SR4

[#2]

Thanks Marco.  I'll play with your (and Rob's) examples.

John

[#3]

Ishfaque,

As I mentioned in a post in the middle of this conversation, this is human entered data.  The bulk of the strings only have single spaces, but a few have more than one space between the fields.

I have to process about 250K rows to see if there is data in the field and if so, if the data is valid.

What I have provided is a subset of the processing I'm doing on each string.

John