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(WECHSELN(KLEIN(RECHTS(B3,(LAENGE(B3))-(SUCHEN(",",B3))-1)&"."&LINKS(B3,(SUCHEN(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSCH)

=WENN(B23="GUL"=WECHSELN(WECHSELN(KLEIN(RECHTS(B3,(LAENGE(B3))-(SUCHEN(",",B3))-1)&"."&LINKS(B3,(SUCHEN(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSCH)

Open in new window

LVL 2
Mandy_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
=WENN(B23="GUL"=WECHSELN(

Should this have a comma:

=WENN(B23="GUL",=WECHSELN(

Thanks
Rob H
0
helpfinderIT ConsultantCommented:
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(WECHSELN(KLEIN
remove = sign infront of WECHSELN
0
Rob HensonFinance AnalystCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Mandy_Author Commented:
if i set a comma the cells behind not marked.  one " more or less also not solve it
0
Mandy_Author Commented:
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(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)
=IF(B23="GUL"=SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)

Open in new window

0
Rob HensonFinance AnalystCommented:
Does the SUBSTITUTE part of the formula work without the IF statement?
0
Rob HensonFinance AnalystCommented:
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?
0
Rob HensonFinance AnalystCommented:
With the following values:

B3 =  jones, mike
B18 = gul.com
B23 = GUL

Formula:

=IF(B23="GUL",SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",),FALSE)

Gives result:

mike.jones@gul.com

Is that what you are expecting? Only change I have made is the comma as originally stated.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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.
0
Mandy_Author Commented:
Great. Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.