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.

It would probably help if you broke up your very long formulas into seperate (hidden?) cells — McKay
– McKay, Commented Nov 10, 2010 at 22:55

McKay · Accepted Answer · 2010-11-11 15:00:32Z

4

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.

edited Nov 11, 2010 at 15:00

answered Nov 10, 2010 at 22:56

McKay

12.7k7 gold badges58 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

McKay Over a year ago

@JohnMerlino I've updated it to get the answer you say you want.

JohnMerlino Over a year ago

I have to apply this to 800,000 records all in a single column

McKay Over a year ago

@JohnMerlino Oh, then first move the calculation cells to the right of the data, and copy all of the calculation records down to all 800,000

JohnMerlino Over a year ago

For me B5 contains this: KNUD J And Excel also gave me a circular reference message.

JohnMerlino Over a year ago

Is your formula tested? I tried it again and got circular reference warning.

|

Collectives™ on Stack Overflow

Excel formula contains error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related