Lookup and report Missing digits

**Sweetypie** · 03-01-2010, 03:53 PM

This is my first request. Hope I state the problem clearly enough. Thanks in advance.
I have a list of say 6 digit numbers in Column A.

I would like to report in say column D all the missing digits (0--9) that are not found in each cell.
E.g.
Say cell A1 has 014579, then the missing digits are 2368 reported in D1 and so on.

**NBVC** · 03-01-2010, 04:29 PM

Add this User Defined function to your VB Editor (Alt+F11, Insert|Module)

Please Login or Register  to view this content.

The use formula:

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

confirmed with CTRL+SHIFT+ENTER not just ENTER..

where A1 contains your number to check.

**Sweetypie** · 03-01-2010, 04:39 PM

Thanks NBVC it worked. Awesome.

**tony h** · 03-01-2010, 04:43 PM

I personally would use VBA but if you don't want to you can use a formula like:

=IF(ISERR(FIND("1",A3,1)),"1","")&IF(ISERR(FIND("2",A3,1)),"2","")&IF(ISERR(FIND("3",A3,1)),"3","")&IF(ISERR(FIND("4",A3,1)),"4","")&IF(ISERR(FIND("5",A3,1)),"5","")&IF(ISERR(FIND("6",A3,1)),"6","")&IF(ISERR(FIND("7",A3,1)),"7","")&IF(ISERR(FIND("8",A3,1)),"8","")&IF(ISERR(FIND("9",A3,1)),"9","")&IF(ISERR(FIND("0",A3,1)),"0","")

Note that this tests cell A3. You will need to change the A3 to which ever cell.

**roxieexcel** · 07-20-2014, 12:57 PM

Hello NBVC,

I like your formula answer for Lookup and missing digits.

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

I try your formula and I get a #NAME? error. Can you help?

You formula looks great but I can’t get it to work. I have tried it with the data supplied by Sweetypie and attempted to modify it to be used with my data.

I work in the airline industry. Our schedules of days of flying are kept using codes left over from the time when memory was expensive. The code is based on 1=Monday, 2= Tuesday etc. Schedules are shown in a column/field as 123 for flights that occur on Monday, Tuesday Wednesday. X123 means the flights are not scheduled for Monday, Tuesday or Wednesday. My goal is to look at the not scheduled codes, those starting with x and convert them into days of flying so I can then easily sort and find all occurrences on a given day or days of week. For example I can search for all schedules that contain a 2 when I want to look at Tuesday flights. Almost forgot blanks cells represent flying 7 days a week, 1234567.

My real data in A1 is 123, 345, ,x7, x15.
I want to see all the flights as positives that are flying not a mix of not flying. I would like to see starting in B1 123, 345, 1234567, 123456,23467.

Any help thoughts or insights are appreciated.

**roxieexcel** · 07-20-2014, 12:59 PM

Hello NBVC,

I like your formula answer for Lookup and missing digits.

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

I try your formula and I get a #NAME? error. Can you help?

You formula looks great but I can’t get it to work. I have tried it with the data supplied by Sweetypie and attempted to modify it to be used with my data.

I work in the airline industry. Our schedules of days of flying are kept using codes left over from the time when memory was expensive. The code is based on 1=Monday, 2= Tuesday etc. Schedules are shown in a column/field as 123 for flights that occur on Monday, Tuesday Wednesday. X123 means the flights are not scheduled for Monday, Tuesday or Wednesday. My goal is to look at the not scheduled codes, those starting with x and convert them into days of flying so I can then easily sort and find all occurrences on a given day or days of week. For example I can search for all schedules that contain a 2 when I want to look at Tuesday flights. Almost forgot blanks cells represent flying 7 days a week, 1234567.

My real data in A1 is 123, 345, ,x7, x15.
I want to see all the flights as positives that are flying not a mix of not flying. I would like to see starting in B1 123, 345, 1234567, 123456,23467.

Any help thoughts or insights are appreciated.

**martindwilson** · 07-20-2014, 01:06 PM

this thread is 4 years old
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.