IFTEXT & SUBSTITUTE in same cell?

To avoid the error you could wrap your formula is =if(ISERROR)) and say something like =if(iserror(your formula),"",else your formula)

=if(iserror(substitute(a1;find("7/eleven - ";a1;10;"7/11-"),"",substitute(a1;find("7/eleven - ";a1;10;"7/11-")

Originally Posted by VKS

=if(iserror(substitute(a1;find("7/eleven - ";a1;10;"7/11-"),"",substitute(a1;find("7/eleven - ";a1;10;"7/11-")

=HVIS(ERFEIL(ERSTATT(A1;FINN("7/eleven - ";A1;10;"7/11-");"";ERSTATT(A1;FINN("7/eleven - ";A1;10;"7/11-")

Can't seem to make this work, it says too many arguments in formula..

It's the norwegian counter-parts of the names in the formula btw. :-)

I'm not sure why you have FIND in there. You can do it like this:

=SUBSTITUTE(A1;"7/ELEVEN - ";"7/11-")

then if there is no match no change is made.

You can change the other texts by nesting them together, like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"7/ELEVEN - ";"7/11-");"WALMART";"WM");"HOME DEPOT";"HD")

Hope this helps.

Pete

This is my actual code..

Please Login or Register  to view this content.

ERSTATT = SUBSTITUTE

This only returns #VALUE for me.

Why have you got ;12 in there? Remove that and see what happens.

Hope this helps.

Pete

Then it said I had too few arguments in the formula. it's 12 there because I read on some webpage I needed it to say how many letters was supposed to be replaced. "REMA 1000 - " is 12 letters.

Still doesn't work when I removed it.. too few arguments in formula.

ERSTATT is equivalent to the REPLACE function in English - you need to be using BYTT.TU instead of SUBSTITUTE.

Hope this helps.

Pete

It had to be translation.. it works now...