[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel  Could you pls check formula if

Posted on 2014-08-28
10
Medium Priority
?
132 Views
Last Modified: 2014-08-28
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

0
Comment
Question by:Mandy_
  • 6
  • 3
10 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40290161
=WENN(B23="GUL"=WECHSELN(

Should this have a comma:

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

Thanks
Rob H
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 40290168
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40290203
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Mandy_
ID: 40290237
if i set a comma the cells behind not marked.  one " more or less also not solve it
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40290261
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40290282
Does the SUBSTITUTE part of the formula work without the IF statement?
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40290296
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
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40290318
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40290345
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
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40290940
Great. Thank you so much
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question