NAND ?

[Gilbert De Ceulaer]

In one cell I have the string "2367"; in another I have "26".
I want to have "37" as a result.
How do I do it, "NAND" not being an available function ?
Thanks,
Gilbert

 

[Toppers]

Try:

=SUBSTITUTE(SUBSTITUTE(A1,LEFT(B1,1),""),RIGHT(B1,1),"")

A1="2367"
B1="26"

HTH

 

[daddylonglegs]

If you need a solution which copes with different length numbers or
strings in A1 and B1, you could use this formula which utilises the
MCONCAT function from Morefunc add-in

=MCONCAT(IF(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)),"",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

confirmed with CTRL+SHIFT+ENTER

 

[Gilbert De Ceulaer]

Thanks, this works with B1 being a 2-character-string but not with "267"
It should work with strings of variable length, as well in A1 as in B1;
so "123456" and "2" should give "13456",
"1234567" and "25" should give "13467",
"1234" and 134" should give "2", etc
Do you have a suggestion ?
Gilbert

 

[Gilbert De Ceulaer]

Dear DLL,
1. This works, except when the result-string = "".
2. I do not understand the formula, but why the "ISNUMBER"-function ? All
the characters are numbers.
Regards,
Gilbert

"daddylonglegs" <[email protected]>
wrote in message

 

[Gilbert De Ceulaer]

Sorry, DDL, it works when the result-string is "", but it doen't work when
the A1-string is ""
Suggestions ?
Gilbert

"daddylonglegs" <[email protected]>
wrote in message

 

[daddylonglegs]

Sorry, DDL, it works when the result-string is "", but it doen't wor
when
the A1-string is ""
Suggestions ?
Gilbert

It should return a blank if A1 is blank. It will return an error if A
contains a "formula blank", i.e "" as the result of a formula. You ca
fix that with a small alteration

=MCONCAT(IF(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:99")),1),B1)),"",MID(A1,ROW(INDIRECT("1:99")),1)))

this assumes that your entry in A1 has less than 100 characters

THis formula should work for numbers or letters (note that if a numbe
of letter occurs once in B1 all instances will be removed)

ISNUMBER is not checking whether A1 is a number but checks whether th
result of the SEARCH function is a number, SEARCH either returns a
error or a number

 

[Leo Heuser]

In one cell I have the string "2367"; in another I have "26".
I want to have "37" as a result.
How do I do it, "NAND" not being an available function ?
Thanks,
Gilbert

Hi Gilbert

Here's an array formula. A1 and B1 can hold numbers as text or number,
but not enclosed in double quotes.

=SUBSTITUTE(SUM(ISERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1))*
10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0,"")

to be entered with <Shift><Ctrl><Enter> instead of <Enter>.