Welcome Guest
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
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.

\$\$\$

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.

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.

\$\$\$

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!

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.

\$\$\$

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

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},""))

\$\$\$

 Page 1 of 1