Welcome Guest
Log In | Register )
You last visited December 8, 2016, 10:57 am
All times shown are
Eastern Time (GMT-5:00)

Excel Help-missing digits

Topic closed. 6 replies. Last post 7 years ago by Fibonacci.

Page 1 of 1
PrintE-mailLink
Fibonacci's avatar - Lottery-050.jpg
New York, NY
United States
Member #39471
May 16, 2006
2696 Posts
Offline
Posted: February 26, 2010, 4:25 pm - IP Logged

I'd like a formula to give the missing diits for any string of digits in an Excel cell.

E.g.

if cell A1 has 123    I want the formula to show 0456789

if cell A1 has 023467  the formula will show 1589

etc.

$$$

    Avatar
    CA
    United States
    Member #84266
    December 26, 2009
    410 Posts
    Offline
    Posted: February 26, 2010, 6:08 pm - IP Logged

    I don't think that is possible.

      Fibonacci's avatar - Lottery-050.jpg
      New York, NY
      United States
      Member #39471
      May 16, 2006
      2696 Posts
      Offline
      Posted: February 27, 2010, 3:13 pm - IP Logged

      I don't think that is possible.

      It is. Basically the formula would check the original cell for digits 0 to 9 and return missing digits in the reporting cell.

      I suspect it might involve the lookup function-- HLookup maybe. My Excel is rusty.

      $$$

        Raven62's avatar - binary
        New Jersey
        United States
        Member #17843
        June 28, 2005
        49773 Posts
        Online
        Posted: February 27, 2010, 3:23 pm - IP Logged

        It is. Basically the formula would check the original cell for digits 0 to 9 and return missing digits in the reporting cell.

        I suspect it might involve the lookup function-- HLookup maybe. My Excel is rusty.

        Their are many ways to do what you want: Just not in one Formula...

        A mind once stretched by a new idea never returns to its original dimensions!

          Fibonacci's avatar - Lottery-050.jpg
          New York, NY
          United States
          Member #39471
          May 16, 2006
          2696 Posts
          Offline
          Posted: February 27, 2010, 8:15 pm - IP Logged

          Their are many ways to do what you want: Just not in one Formula...

          I just need 1 way. Grateful for any.

          $$$

            Avatar
            Milwaukee, WI
            United States
            Member #3131
            December 27, 2003
            665 Posts
            Offline
            Posted: February 27, 2010, 8:35 pm - IP Logged

            I haven't used Excel, or haven't needed that function but in formatting in open office that have something called user-defined.

            So look in the formatting help section under number formatting...

            Best help that I can give you...

            MarkP

              Fibonacci's avatar - Lottery-050.jpg
              New York, NY
              United States
              Member #39471
              May 16, 2006
              2696 Posts
              Offline
              Posted: March 1, 2010, 3:54 pm - IP Logged

              Got it! If anyone wants to use this here it is. It works for me (Excel 2007). Thanks to an online Excel forum helper.

              Step 1

              Add this fucntion to VB (alt F11) Insert Module

              Function aconcat(a As Variant, Optional sep As String = "") As String
              ' Harlan Grove, Mar 2002
              Dim y As Variant

              If TypeOf a Is Range Then
              For Each y In a.Cells
              aconcat = aconcat & y.Value & sep
              Next y
              ElseIf IsArray(a) Then
              For Each y In a
              aconcat = aconcat & y & sep
              Next y
              Else
              aconcat = aconcat & a & sep
              End If

              aconcat = Left(aconcat, Len(aconcat) - Len(sep))
              End Function

              Step 2 the formula (assuming 6-digit string is in A1)

              =aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),{0,1,2,3,4,5,6,7,8,9},""))

              $$$