IF a cell only contains a specific character then reformat the tex
 

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)

IF a cell only contains a specific character then reformat the tex
 

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)

IF a cell only contains a specific character then reformat the tex
 

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

IF a cell only contains a specific character then reformat the
 

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)