Parse cell value based on contents
 

In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Try this:

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Ooops!

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND ("(",A1)+1,100),")",""))

You wanted the cell left blank...

=IF(ISERR(FIND("(",A1)),"",SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Look at this example:

=if(iserror(YourFormula),"",YourFormula))

Regards,
Per

On 24 Sep., 04:42, Craig860
wrote:
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Another suggestion - which I got from one of your previous posts:

=SUBSTITUTE(MID(A1,FIND("(",A1&"(")+1,255),"(","")


"T. Valko" wrote:

Ooops!

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND ("(",A1)+1,100),")",""))

You wanted the cell left blank...

=IF(ISERR(FIND("(",A1)),"",SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Nice one! <g

Just turn around that last instance of "("

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
Another suggestion - which I got from one of your previous posts:

=SUBSTITUTE(MID(A1,FIND("(",A1&"(")+1,255),"(","")


"T. Valko" wrote:

Ooops!

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND ("(",A1)+1,100),")",""))

You wanted the cell left blank...

=IF(ISERR(FIND("(",A1)),"",SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank,
how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I
get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Give yourself a pat on the back - you earned it! I must have copied the
formula to the clipboard before I fixed that parentheses thingy:

=SUBSTITUTE(MID(A1,FIND("(",A1&"(")+1,255),")","")


"T. Valko" wrote:

Nice one! <g

Just turn around that last instance of "("

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
Another suggestion - which I got from one of your previous posts:

=SUBSTITUTE(MID(A1,FIND("(",A1&"(")+1,255),"(","")


"T. Valko" wrote:

Ooops!

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND ("(",A1)+1,100),")",""))

You wanted the cell left blank...

=IF(ISERR(FIND("(",A1)),"",SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=IF(ISERR(FIND("(",A1)),A1,SUBSTITUTE(MID(A1,FIND( "(",A1)+1,100),")",""))


--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank,
how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I
get
#VALUE! which makes the spreadsheet look sloppy.

Parse cell value based on contents
 

Thank you gentlemen! I don't think I would have ever come up with that.

"Craig860" wrote:

In follow up to my previous question,
if
A1=Joe(abc)
a2=Sam(bdc)
a3=Sarah

to get the contents of the paranthesis in b1,b2 however b3 be blank, how
would would the formula go so I can avoid #VALUE! in b3.
ie:
b1=abc
b2=bdc
b3=
=MID(G2,FIND("(",G2)+1,(FIND(")",G2)-FIND("(",G2)-1)) will give me the
parenthisi value in it's own cell. But if there is no parenthisis, I get
#VALUE! which makes the spreadsheet look sloppy.