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))))
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.

NorieVBA ExpertCommented:
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
NorieVBA ExpertCommented:
What do you want to replace B18 with?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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

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
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
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.