[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

excel "missing arguments"

Dear experts,

maybe anybody can help me to find the error. I'm getting "missing arguments" with the formula below.
Thanks in advance.

=IF(K24="[GUL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GAL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GIL]",IFERROR(GELWECHSELN(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GoL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GEL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16))))

Open in new window


=IF(K24="[GUL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GAL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GIL]",IFERROR(GELWECHSELN(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GoL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16),IF(K24="[GEL]",IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(K3,(LEN(K3))-(SEARCH(",",K3))-1)&"."&LEFT(K3,(SEARCH(",",K3)-1))&"@"&K16))))
0
Mandy_
Asked:
Mandy_
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
NorieCommented:
What is the formula meant to do?

As far as I can seet you are missing arguments from SUBSTITUTE, IFERROR and IF.

Also, there's a function I don't recognise in there, GELWECHSELN.

Is that meant to be SUBSTITUTE in German?

PS It looks like you are repeating the same SUBSTITUTE formula 5, if you are you could use OR.
0
 
Mandy_Author Commented:
=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&B18)

This formula above is working. Now i like to change the parameter for B18 with 4 different values in B24
B18 is the suffix of an email address. I build the formula in my 1st post. The correct error message is: You have entered too few arguments for this function.
0
 
NorieCommented:
What do you want to replace B18 with?
0
Industry Leaders: 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!

 
Mandy_Author Commented:
depends on Cell B24 or K24  (gul.com,gil.com,gol.com,gal.com)

if b24 = gul  = gul.com
if b24 = gil = gil.com
....
.....
0
 
Rgonzo1971Commented:
pls try

=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&B18)," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&LOWER(B24)&".com")

do not use [] in a string expression these are not fields
0
 
Mandy_Author Commented:
Thanks for your comment. B24 are also change different formulas depends on the values (GUL, GIL, GAL, GEL)
in dropdown list. If select GUL at B24 it use also different Exchange Databases and smtp suffixes as GAL or GIL
The suffixes are stored in B18,B19;B20,B21 and should change also in your formula depending on B24 and
company to 4 different suffixes values.

I'm using IF(B24="[GUL]" in other formulas with " [ ] " without any problems but i can change that to "GUL" only.
0
 
Rgonzo1971Commented:
Do you mean

if B24 = GUL then suffix is B18
if B24 = GIL then suffix is B19
if B24 = GAL then suffix is B20
if B24 = GEL then suffix is B21
0
 
Rob HensonIT & Database AssistantCommented:
How about setting up a small lookup table:
      Col A  Col B
18  GUL   gul.com
19  GIL    gil.com
20  GAL   gal.com
21  GEL   gel.com

Then this section:
"&"@"&B18)"

would change to:

"&"@&VLOOKUP(B24,$A$18:$B$21,2,false))

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
I think the issue is in the SUBSTITUTE functions. The syntax for this function is:

=SUBSTITUTE(text,old text,new text,instance num)

text - the cell to look at
old text - the text to find within cell
new text - the text to replace old text
instance num - optional, decides how many occurences it converts

At the moment your LOWER function is determining where to look, but I suspect this should be supplying what to replace with. The LOWER function will convert without the need for SUBSTITUTE if you have no characters that you want to ignore eg commas or slashes.

I assume this is a follow on from your previous question with which I helped before.

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Ignore my previous comment,  was misreading the formula.

See attached example with this formula:

=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&VLOOKUP(B24,A18:B21,2,FALSE))," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&VLOOKUP(B24,A18:B21,2,FALSE))

Do you know what would cause the first calculation within the IFERROR to flag an error and therefore use the second option?

Thanks
Rob H
SUBSTITUTE.xlsx
0
 
Mandy_Author Commented:
Thanks Rob. I will try that.


@Rgonzo1971

if B24 = GAL then suffix is B21,B20
if B24 = GAL-1 then suffix is B21,B20
if B24 = GUL then suffix is B21, B19
if B24 = GUL-1 then suffix is B21,B19
if B24 = GIL then suffix is B22,B23,B20
0
 
Mandy_Author Commented:
@rob
I've tried your sheet and it's would work if some changes will made. Pls see attachment.
Also would be nice to write the complete result in small letters.

Thanks in advance
mandy
SUBSTITUTE.xlsx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now