Mandy_
asked on
excel Could you pls check formula if
Dear experts,
could you pls check the formula. It seems that a ")" is missing but i cannot find the problem. it says "wrong" thank you
=WENN(B23="GUL"=WECHSELN(W
could you pls check the formula. It seems that a ")" is missing but i cannot find the problem. it says "wrong" thank you
=WENN(B23="GUL"=WECHSELN(W
=WENN(B23="GUL"=WECHSELN(WECHSELN(KLEIN(RECHTS(B3,(LAENGE(B3))-(SUCHEN(",",B3))-1)&"."&LINKS(B3,(SUCHEN(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSCH)
if you think there is just a syntax error try to check this part
... &B18)," ",""),"/,",),FALSCH)
where it seems you have one " sign more
also try here
=WENN(B23="GUL"=WECHSELN(W
remove = sign infront of WECHSELN
... &B18)," ",""),"/,",),FALSCH)
where it seems you have one " sign more
also try here
=WENN(B23="GUL"=WECHSELN(W
remove = sign infront of WECHSELN
My excel won't accept the formula as I have English but this is in German. However, copying and pasting into a cell shows all brackets are paired off, they highlight in colours in pairs so it is not a number of brackets issue, however it could be a placing of brackets issue if it is not the missing comma as originally suggested.
basis of original suggestion is that I am assuming the WENN translates to IF. If so I think the first part of the formula is equivalent to:
=IF(B23="GUL",SUBSTITUTE(SUBSTITUTE(
Thanks
Rob H
basis of original suggestion is that I am assuming the WENN translates to IF. If so I think the first part of the formula is equivalent to:
=IF(B23="GUL",SUBSTITUTE(SUBSTITUTE(
Thanks
Rob H
ASKER
if i set a comma the cells behind not marked. one " more or less also not solve it
ASKER
maybe i should explain. it takes from a name in cell B3 like "jones, mike" (firstname, lastname) and build mike.jones@gul.com with value of B18
sorry here the english translation
=IF(B23="GUL"=SUBSTITUTE(S
sorry here the english translation
=IF(B23="GUL"=SUBSTITUTE(S
=IF(B23="GUL"=SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)
Does the SUBSTITUTE part of the formula work without the IF statement?
Exchanging a comma for the = before the first SUBSTITUTE works for me and gives a FALSE as expected when B23 does not equal "GUL".
Can you give a sample of what is in B3 so that we can check the SUBSTITUTE formula when B23 does equal "GUL"?
Thanks
Rob H
EDIT: Sorry, just spotted "jones, mike" example in previous comment. What is in B18?
Can you give a sample of what is in B3 so that we can check the SUBSTITUTE formula when B23 does equal "GUL"?
Thanks
Rob H
EDIT: Sorry, just spotted "jones, mike" example in previous comment. What is in B18?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Trying to see reason for the "/" section.
I think this could be causing your error.
That SUBSTITUTE is currently looking for "/," but doesn't have a replacement parameter; I think that parameter is required whereas the last parameter for number of instances is optional.
Changing that section to:
"/,",""),FALSE) or "/",""),FALSE) depending on whether you are looking for "/," or just "/" works.
Thanks
Rob H
EDIT: Scrub the comment about requiring "replacement" parameter, seems to work without and replaces with nothing.
I think this could be causing your error.
That SUBSTITUTE is currently looking for "/," but doesn't have a replacement parameter; I think that parameter is required whereas the last parameter for number of instances is optional.
Changing that section to:
"/,",""),FALSE) or "/",""),FALSE) depending on whether you are looking for "/," or just "/" works.
Thanks
Rob H
EDIT: Scrub the comment about requiring "replacement" parameter, seems to work without and replaces with nothing.
ASKER
Great. Thank you so much
Should this have a comma:
=WENN(B23="GUL",=WECHSELN(
Thanks
Rob H