Excel formula contains error

Question

I have an error in this excel formula and I can't just figure it out:

=LEFT(B3,FIND(",",B3&",")-1)&","&RIGHT(B3,LEN(B3)-FIND("&",B3&"&")),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))&", "&SUBSTITUTE(RIGHT(B3,LEN(B3)-FIND("&",B3&"&")-1),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))),""))

It may seem like a big formula, but all it's intended to do is if no ampersand is in a cell, return an empty cell, if no comma but ampersand exists, then return this, for example:

KNUD J & MARIA L HOSTRUP

into this:

HOSTRUP,MARIA L

Otherwise, there is no ampersand but there is a comma so we just return: LEFT(A1,FIND("&",A1,1)-1).

Seems basic, but formula has been giving me error message and doesn't point to the problem.

Answer 1

Your error is here:

=LEFT(B3,FIND(",",B3&",")-1)&","&RIGHT(B3,LEN(B3)-FIND("&",B3&"&")),

At this point, the comma doesn't apply to anthing, because the right operator has matching parens

As far as what you want? Let's break that up into what you actually asked for:

if no ampersand in a cell, return empty cell,

B4=Find("&", B3&"&")
B5=IF(B4>LEN(B3),"",B6)

if no comma but ampersand exists

B6=IF(FIND(",", B3&",")>LEN(B3),B8,B7)

then turn this, for example:

KNUD J & MARIA L HOSTRUP into this: HOSTRUP,MARIA L

I'm presuming you mean to put the last whole word? Let's mark the last whole word:

B9=SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
B10=RIGHT(B7,LEN(B9)-FIND("@",B9))

And the stuff between the ampersand and the last word

B11=TRIM(MID(B9,B4 + 1, LEN(B9)-FIND("@",B9)-1))

Then calculating it is easy

B7=B10&","&B11

Otherwise, there is no ampersand but there is a comma so we just return: LEFT(A1,FIND("&",A1,1)-1).

Well, if you want that, let's just put that in B8

B8=LEFT(A1,FIND("&",A1,1)-1)

(But I think you actually mean B3 instead of A1)

B8=LEFT(B3,FIND("&",B3,1)-1)

And there you have it (B5 contains the information you're looking for) It took a few cells, but it's easier to debug this way. If you want to collapse it, you can (but doing so is more code, because we can reduce duplication by referencing a previously calculated cell on more than one occasion).

Summary:

B3=<Some Name with & or ,>
B4=FIND("&", B3&"&")
B5=IF(B4>LEN(B3),"",B6)
B6=IF(FIND(",", B3&",")>LEN(B3),B7,B8)
B7=B10&","&B11
B8=LEFT(B3,FIND("&",B3,1)-1)
B9=SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
B10=RIGHT(B9,LEN(B9)-FIND("@",B9))
B11=TRIM(MID(B9,B4 + 1, LEN(B9)-FIND("@",B9)-1))

When I put in "KNUD J & MARIA L HOSTRUP", I get "HOSTRUP,MARIA" in B5.