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



--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2560 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

Try:

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

A1="2367"
B1="26"

HTH

"Gilbert De Ceulaer" wrote:

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



--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2560 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

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:"&L EN(A1))),1)))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566278

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

"Toppers" wrote in message
...
Try:

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

A1="2367"
B1="26"

HTH

"Gilbert De Ceulaer" wrote:

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



--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2560 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!




--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2564 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

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"
wrote in message
news:daddylonglegs.2bprpg_1154188208.0303@excelfor um-nospam.com...

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:"&L EN(A1))),1)))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566278


--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2591 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

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

"daddylonglegs"
wrote in message
news:daddylonglegs.2bprpg_1154188208.0303@excelfor um-nospam.com...

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:"&L EN(A1))),1)))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566278


--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
2591 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

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



It should return a blank if A1 is blank. It will return an error if A1
contains a "formula blank", i.e "" as the result of a formula. You can
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 number
of letter occurs once in B1 all instances will be removed)

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566278

"Gilbert De Ceulaer" skrev i en meddelelse
...
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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.