Help on visible data

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
 
R

Roger Govier

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"
 
S

Stephen

"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
 
G

Guest

=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))
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Visible Data 6
Formula with Cell reference to include formating? 13
Excel Need Countifs Formula Help 0
Continuous Invoice Nos. 3
Excel Copy COUNTIF while retaining reference 7
How do I anchor a cell reference? 3
keeping format of a referenced cell 4
Fixed/Locked cell referencing 6

Top