Mandy_
asked on
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(SU
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))))=IF(K24="[GUL]",IFERROR(SU
ASKER
=IFERROR(SUBSTITUTE(SUBSTI
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.
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.
What do you want to replace B18 with?
ASKER
depends on Cell B24 or K24 (gul.com,gil.com,gol.com,g
if b24 = gul = gul.com
if b24 = gil = gil.com
....
.....
if b24 = gul = gul.com
if b24 = gil = gil.com
....
.....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
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$
Thanks
Rob H
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$
Thanks
Rob H
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
=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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
@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
ASKER
@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
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
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.