How to find strings with only one number different from 0

**simone77** · 03-27-2012, 01:17 PM

Very long title, sorry, but I didn't find any other way to say that.

I have cells formatted like text with a code of length, say, five that may contain "0", "1", "2", "3".
Something like that:

01100
10200
30210
00200
10200
00003
01000

I want to find (to sort and eliminate them) all those cells with just one value different from zero. In the example would be the 4th, 6th, and 7th.
Any hint?

**Ron Coderre** · 03-27-2012, 01:23 PM

Can you use a formula like this to flag those specific items?
B1: =LEN(SUBSTITUTE(A1,0,""))>1

**simone77** · 03-27-2012, 01:25 PM

Great! It works fine.
Thanks

Originally Posted by Ron Coderre

Can you use a formula like this to flag those specific items?
B1: =LEN(SUBSTITUTE(A1,0,""))>1

**dilipandey** · 03-27-2012, 01:28 PM

Hi simone,

What about multiplying the data with 1 - to convert them into numerics
then create a column to calculate length - use LEN function here

now filter out data which are less than or equal to 1 using LEN column and delete them.. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>