+ Reply to Thread
Results 1 to 4 of 4

How to find strings with only one number different from 0

  1. #1
    Registered User
    Join Date
    11-09-2011
    Location
    Seville
    MS-Off Ver
    Excel 2007
    Posts
    91

    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. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-09-2011
    Location
    Seville
    MS-Off Ver
    Excel 2007
    Posts
    91

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

    Great! It works fine.
    Thanks

    Quote Originally Posted by Ron Coderre View Post
    Can you use a formula like this to flag those specific items?
    B1: =LEN(SUBSTITUTE(A1,0,""))>1

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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