Help on visible data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I type data into a cell, I would like anything that is typed in
brackets () to be excluded when referenced by another cell. The data in the
original cell would be variable and may or may not include brackets

ie A1 = I would like (to exclude the data in brackets) when referenced in
cell A2
A2 = I would like when referenced in cell A2

The following formula provided by T.Valko helped immensely

=LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND(")",A1)+1,255)

However, it assumes that there is always a set of ( ) in the referenced
cell. If there are no brackets, I would still like the referrenced cell to
show all the data. At present it returns a Value error.

Any help would be appreciated
 
Hi

Perhaps
=IF(NOT(ISNUMBER(FIND("(",A1))),A1,LEFT(A1,FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,255))

--
Regards
Roger Govier



"I think I need to rephrase the question"
 
"I think I need to rephrase the question"
When I type data into a cell, I would like anything that is typed in
brackets () to be excluded when referenced by another cell. The data in
the
original cell would be variable and may or may not include brackets

ie A1 = I would like (to exclude the data in brackets) when referenced in
cell A2
A2 = I would like when referenced in cell A2

The following formula provided by T.Valko helped immensely

=LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND(")",A1)+1,255)

However, it assumes that there is always a set of ( ) in the referenced
cell. If there are no brackets, I would still like the referrenced cell to
show all the data. At present it returns a Value error.

Any help would be appreciated
Click to expand...

You need to embed the above formula inside one that checks for the presence
of brackets, such as
=IF(ISNUMBER(FIND("(",A1)),LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND(")",A1)+1,255),A1)

Stephen
 
=IF(ISERROR(LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND(")",A1)+1,255)),A1,LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND(")",A1)+1,255))
 
Thanks Roger, that works perfectly

Roger Govier said:
Hi

Perhaps
=IF(NOT(ISNUMBER(FIND("(",A1))),A1,LEFT(A1,FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,255))

--
Regards
Roger Govier



"I think I need to rephrase the question"
Click to expand...
 
Back
Top