parsing data

Posted by mcarter973 on May 22, 2001 1:04 PM

here's an example:

ABCD1
ABCDEFG2

What formula can extract all 1's and sort by that column?

Thanks.

Posted by Dax on May 22, 2001 1:12 PM

Hello,
Not sure if this is what you mean but I'll put it here anyway. This formula will return True if the cell in column A contains a 1 and False if not which you can then sort by.

=ISERROR(FIND(1,A1))

Regards,
Dax.

Posted by Dax on May 22, 2001 1:14 PM

Re: slight change to above formula

Hello,
Not sure if this is what you mean but I'll put it here anyway. This formula will return True if the cell in column A contains a 1 and False if not which you can then sort by.

=ISERROR(FIND(1,A1))

Regards,
Dax.

Change that to: -

=NOT(ISERROR(FIND(1,A8)))

I had the False and True the wrong way around in the previous post.

Posted by Dave Hawley on May 23, 2001 2:20 AM

Hi mcarter973

This formula will all intances of 1 in cell.

=REPT(1,LEN(A1)-LEN(SUBSTITUTE(A1,1,"")))

Dave

OzGrid Business Applications