Filtering numbers with and without spaces in large SS

[Ben Morton]

Hi there,


I have a SS that has 20,000 rows.

One of the columns contains numbers.

I'm tryiny to filter out the numbers that are NOT 7 digits long.

The problem i have is that some of the cells have space in between the numbers.

e.g 240 1332, 432 2443, 234 2345 234 etc.

And some are normal e.g 4234535, 53596835, 3459284, 2423 etc

So, filtering between ranges isn't working i think because numbers with spaces in them aren't read as numbers in excel.

Cheers for any idea!

[Richard Buttrey]

Hi,

Use a helper column and enter

Please Login or Register  to view this content.

Now copy down your list and filter the helper column for the values that are not 7 and delete those rows.

HTH

[Ben Morton]

Richard,

Thanks a millon!

I changed the cell reference in your formula to the column with the numbers and it worked a treat!

Cheers

Ben.

[Ben Morton]

Ahh,

I think I see what you've done - if the LEN function finds a space it takes one of the total. Very neat.

Thanks again.

[Richard Buttrey]

Indeed,

However I now realise it's more complicated than need be. I was thinking you might want to recreate the number without a space, hence the string slicing and adding the two sets of digits either side of the space.

Please Login or Register  to view this content.

would have done just as well.

Rgds