Including spaces in filters

Hi,

I have a list where some entries are preceded by 5 spaces, others by 7 spaces.

When I try to custom filter this list to only display those that begin after 5 spaces by filtering to;

'Does not begin with ¦ <7 spaces>*'

It doesn't display any entries from the list. When I go back to the filter it has automatically udated it to;

'Does not contain ¦ *'

I need to keep the list in order so I cannot sort it. Any ideas??

Thanks in advance,
Steve

Can you not just remove all the spaces?
Regards,
Alan.
"steev_jd" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have a list where some entries are preceded by 5 spaces, others by 7
> spaces.
>
> When I try to custom filter this list to only display those that begin
> after 5 spaces by filtering to;
>
> 'Does not begin with ¦ <7 spaces>*'
>
> It doesn't display any entries from the list. When I go back to the
> filter it has automatically udated it to;
>
> 'Does not contain ¦ *'
>
> I need to keep the list in order so I cannot sort it. Any ideas??
>
> Thanks in advance,
> Steve
>
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile:
> http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=569919
>

One idea...

You could identify which type of record is which in another column, then
filter all the data on that column. For example, if your data begins in A2
with a heading in A1, in the first empty column, enter any heading in row 1
and the following in row 2:

=IF(LEN(TRIM(A2)) = LEN(A2) - 5,"5","7")

Copy this down through all rows of data, then filter all data by this column.

Hope this helps,

Hutch

"steev_jd" wrote:

>
> Hi,
>
> I have a list where some entries are preceded by 5 spaces, others by 7
> spaces.
>
> When I try to custom filter this list to only display those that begin
> after 5 spaces by filtering to;
>
> 'Does not begin with ¦ <7 spaces>*'
>
> It doesn't display any entries from the list. When I go back to the
> filter it has automatically udated it to;
>
> 'Does not contain ¦ *'
>
> I need to keep the list in order so I cannot sort it. Any ideas??
>
> Thanks in advance,
> Steve
>
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=569919
>
>

Just realized that trailing spaces would throw off the formula I gave you in
my previous reply. Use this formula instead:

=IF(LEN(TRIM(LEFT(A2,7))) = 2,"5","7")

Regards,

Hutch

"steev_jd" wrote:

>
> Hi,
>
> I have a list where some entries are preceded by 5 spaces, others by 7
> spaces.
>
> When I try to custom filter this list to only display those that begin
> after 5 spaces by filtering to;
>
> 'Does not begin with ¦ <7 spaces>*'
>
> It doesn't display any entries from the list. When I go back to the
> filter it has automatically udated it to;
>
> 'Does not contain ¦ *'
>
> I need to keep the list in order so I cannot sort it. Any ideas??
>
> Thanks in advance,
> Steve
>
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=569919
>
>

Hi,

Thank you very much for your answers. Though unfortunatley having the entries in a seperate list or deleting the spaces won't help.

The list is laid out as such
<5 spaces> PARA ¦ 100
<7 spaces> HERT ¦ 80
<7 spaces> KULR ¦ 20
<5 spaces> GIHI ¦ 5
<7 spaces> JUBS ¦ 2
<7 spaces> TEFT ¦ 2
<5 spaces> JMLU ¦ 500
<7 spaces> HTYE ¦ 250
<7 spaces> ERWE ¦ 150
<7 spaces> FERT ¦ 100

and I have to check that the value of the 5 spaced items equals the total of 7 spaced items mentioned beneth

i.e. above PARA and JMLU would be fine, GIHI wouldn't.

Do you have any furter ideas?

(the entries may have more than 4 letters)

Regards,
Steve

Use Filter > Contains > ? Red
? stands for any character, space between Red and ? stands for Space.