| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on visible data
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on visible data
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" osoft.com wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on visible data
"I think I need to rephrase the question"
osoft.com wrote in message ... 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 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on visible data
=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))
"I think I need to rephrase the question" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on visible data
Thanks Roger, that works perfectly
"Roger Govier" wrote: 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" osoft.com wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on visible data
Thankyou everyone for your help. Much appreciated
"I think I need to rephrase the question" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visible Data | Excel Discussion (Misc queries) | |||
| Print Visible Data Only | Excel Discussion (Misc queries) | |||
| Print Visible Data Only | Excel Worksheet Functions | |||
| how do I sum only visible data in a column | Excel Discussion (Misc queries) | |||
| Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) | |||
Linear Mode
