Hello, I was wondering if someone could steer me in the right direction.
Here is my sample:
A1=GEORGE(ABC)
A2=BOB
A3=SAM (WMC)

I would like to get:
B1=George
B2=Bob
A3=Sam

c1=ABC
c2=
c3=WMC

I'm trying to use an IF statement to look at ie: cell a1 and covert it from
all caps to proper first letter cap and the rest lower, and remove the (ABC).
I used: =MID(a1,FIND("(",a1)+1,(FIND(")",a1)-FIND("(",a1)-1)) which put the
ABC in it's own field C1.
my problem is that when I copy down that formula to ie: A2 i get the #Value!
error because there is no parenthis in that field.
Since i have about 1000 records, i'd like to use just the same formula for
the whole thing but I can't get the darn IF statement to work. It keeps
throwing me a REF! error. If haven't use IF that much so i'm sure i'm using
it incorrectly.
=IF(FIND("(",A3),PROPER(LEFT(A3,FIND("(",A3)-1)),PROPER(A3))
Basically look at A3, if A3 contains a "(" then give me the proper(A3)
without the (WMC). If it doesn't contain "(" then just give me proper(A3)

Rather than IF(FIND( ), ... ), try
IF(NOT(ISERROR(FIND( ))), ...)
The problem is that when the FIND fails, you've got #VALUE, where the IF is
looking for TRUE or FALSE; the NOT(ISERROR( )) will return TRUE if the find
succeeded and false otherwise. (You could leave out the NOT, and reverse the
true/false actions)

"Craig860" wrote:

Hello, I was wondering if someone could steer me in the right direction.
Here is my sample:
A1=GEORGE(ABC)
A2=BOB
A3=SAM (WMC)

I would like to get:
B1=George
B2=Bob
A3=Sam

c1=ABC
c2=
c3=WMC

I'm trying to use an IF statement to look at ie: cell a1 and covert it from
all caps to proper first letter cap and the rest lower, and remove the (ABC).
I used: =MID(a1,FIND("(",a1)+1,(FIND(")",a1)-FIND("(",a1)-1)) which put the
ABC in it's own field C1.
my problem is that when I copy down that formula to ie: A2 i get the #Value!
error because there is no parenthis in that field.
Since i have about 1000 records, i'd like to use just the same formula for
the whole thing but I can't get the darn IF statement to work. It keeps
throwing me a REF! error. If haven't use IF that much so i'm sure i'm using
it incorrectly.
=IF(FIND("(",A3),PROPER(LEFT(A3,FIND("(",A3)-1)),PROPER(A3))
Basically look at A3, if A3 contains a "(" then give me the proper(A3)
without the (WMC). If it doesn't contain "(" then just give me proper(A3)

And instead of using:
=if(not(iserror(find(...

you could use:
=if(isnumber(find(....

a little easier to read.



bpeltzer wrote:

Rather than IF(FIND( ), ... ), try
IF(NOT(ISERROR(FIND( ))), ...)
The problem is that when the FIND fails, you've got #VALUE, where the IF is
looking for TRUE or FALSE; the NOT(ISERROR( )) will return TRUE if the find
succeeded and false otherwise. (You could leave out the NOT, and reverse the
true/false actions)

"Craig860" wrote:

Hello, I was wondering if someone could steer me in the right direction.
Here is my sample:
A1=GEORGE(ABC)
A2=BOB
A3=SAM (WMC)

I would like to get:
B1=George
B2=Bob
A3=Sam

c1=ABC
c2=
c3=WMC

I'm trying to use an IF statement to look at ie: cell a1 and covert it from
all caps to proper first letter cap and the rest lower, and remove the (ABC).
I used: =MID(a1,FIND("(",a1)+1,(FIND(")",a1)-FIND("(",a1)-1)) which put the
ABC in it's own field C1.
my problem is that when I copy down that formula to ie: A2 i get the #Value!
error because there is no parenthis in that field.
Since i have about 1000 records, i'd like to use just the same formula for
the whole thing but I can't get the darn IF statement to work. It keeps
throwing me a REF! error. If haven't use IF that much so i'm sure i'm using
it incorrectly.
=IF(FIND("(",A3),PROPER(LEFT(A3,FIND("(",A3)-1)),PROPER(A3))
Basically look at A3, if A3 contains a "(" then give me the proper(A3)
without the (WMC). If it doesn't contain "(" then just give me proper(A3)


--

Dave Peterson

ahhh! That makes sense! Here's what I came up with:
=IF(NOT(ISERROR(FIND("(",a3)-1)),PROPER((LEFT(a3,FIND("(",a3)-1))),PROPER(a3))

Thanks a lot!

"bpeltzer" wrote:

Rather than IF(FIND( ), ... ), try
IF(NOT(ISERROR(FIND( ))), ...)
The problem is that when the FIND fails, you've got #VALUE, where the IF is
looking for TRUE or FALSE; the NOT(ISERROR( )) will return TRUE if the find
succeeded and false otherwise. (You could leave out the NOT, and reverse the
true/false actions)

"Craig860" wrote:

Hello, I was wondering if someone could steer me in the right direction.
Here is my sample:
A1=GEORGE(ABC)
A2=BOB
A3=SAM (WMC)

I would like to get:
B1=George
B2=Bob
A3=Sam

c1=ABC
c2=
c3=WMC

I'm trying to use an IF statement to look at ie: cell a1 and covert it from
all caps to proper first letter cap and the rest lower, and remove the (ABC).
I used: =MID(a1,FIND("(",a1)+1,(FIND(")",a1)-FIND("(",a1)-1)) which put the
ABC in it's own field C1.
my problem is that when I copy down that formula to ie: A2 i get the #Value!
error because there is no parenthis in that field.
Since i have about 1000 records, i'd like to use just the same formula for
the whole thing but I can't get the darn IF statement to work. It keeps
throwing me a REF! error. If haven't use IF that much so i'm sure i'm using
it incorrectly.
=IF(FIND("(",A3),PROPER(LEFT(A3,FIND("(",A3)-1)),PROPER(A3))
Basically look at A3, if A3 contains a "(" then give me the proper(A3)
without the (WMC). If it doesn't contain "(" then just give me proper(A3)