IF function where the first result returns #VALUE-CodePudding

I've got the following in Column A:

112234
113456(Producer)
123211
114523(Producer)
224431(Producer)

I want to create a Column B and a Column C that separates this out to:

112234
113456         (Producer)
123211
114523         (Producer)
224431         (Producer)

So I put this IF statement in Column B, but it gives me a #VALUE for the 1st and 3rd records:

=IF(FIND("(",A2)>1,LEFT(A2,FIND("(",A2)-1),A2)

Can anyone tell me what I'm doing wrong?

CodePudding user response：

FIND() returns an error when what you are looking for isn't found, not 0.

Try this instead.

=IF(ISERROR(FIND("(",A2)),A2,LEFT(A2,FIND("(",A2)-1))

CodePudding user response：

Are the numbers always 6 digits? If so, I think nothing would stop you from just using text-to-columns.

The current error is the result of FIND() not actually returning any number to compare in your IF(). You could bypass this by a simple concatenation to assert an opening paranthesis does exist:

Formula in B1:

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

Btw, I'm not sure if the data after the opening paranthesis would always be "Producer", but just in case it isn't you could use the following in C1:

=REPLACE(A1,1,LEN(B1),"")