# How to find strings with only one number different from 0

1. ## How to find strings with only one number different from 0

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?

2. ## Re: How to find strings with only one number different from 0

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

3. ## Re: How to find strings with only one number different from 0

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

4. ## Re: How to find strings with only one number different from 0

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>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1