Extract not used numbers !

[ionelz]

Hi,

A1=123456789.

If B1=59173 I need at C1=2468

If B1=9256 and B2=1932 I need at C2=478

Thank all

[Richard Buttrey]

Hi,

This is provably a much simplified example and may not represent the size of your actual data.
We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

For instance are the values always numeric or may some be strings, how long are the numerics/strings, are there alphanumeric cells, is there ever stuff at B1, B2 & B3 which needs eliminating from C3...etc

[ionelz]

A1, B1, B2 are numerical.

[Richard Buttrey]

...and is there ever a B3 and is there a limit to the number of digits

[Pete_UK]

And is the digit zero not counted as a number ?

Pete

[ionelz]

Yes, zero do not exist, only 1 to 9
Numbers in B1 and B2 , exists in A1

[GeoffW283]

Lot's of what-if's per Richard and Pete's concerns but here's an attempt that at least matches the provided example.

ionelz.png

I need a couple of helper ranges.
In D1:K1:

Please Login or Register  to view this content.

In D2:K2 almost the same formula

Please Login or Register  to view this content.

In C1 copied down to C2:

Please Login or Register  to view this content.

textjoin() would have been nice here, unfortunately I have an old version of Excel.

[ionelz]

Thank you very much...

[GeoffW283]

A fun little problem - I'm sure there's more efficient solutions out there! - thanks for the rep!

[Phuocam]

Or try:

=SUBSTITUTE(SUMPRODUCT({1;2;3;4;5;6;7;8;9}*10^{9;8;7;6;5;4;3;2;1}*ISERROR(SEARCH({1;2;3;4;5;6;7;8;9},$B$1&$B$2))),0,"")

[ionelz]

Last one is a cool formula too !
I want to ask if $B$1 or $B$2 can be replaced by a Range

[Phuocam]

The Search function only works with one string.

[Bo_Ry]

Please try at C1

=NPV(-0.9,IF(ISERR(FIND(MID(A1,10-ROW(A$1:A$9),1),B1)),MID(A1,10-ROW(A$1:A$9),1)/10))

Ctrl+Shift+Enter