Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
Hi,
I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
Hi,
For the first number =LEFT(A1,FIND(" ",A1))*1 and the second =MID(A1,FIND("(",A1)+1,(FIND(")",A1,1))-(FIND("(",A1,1)+1))*1 Drag both down for subsequent rows. Mike "edeaston" wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
Hi
For the first number =--(LEFT(A1,FIND("(",A1)-1)) For the second number =--(SUBSTITUTE(MID(A1,FIND("(",A1)+1,9),")","")) The double unary minus -- in front of each formula is just to coerce the values from being text numbers to numeric. -- Regards Roger Govier "edeaston" wrote in message ... Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
you can use text to column option
select the column go to data | text to columns | delimited | next | check : space | next | values which are in brackets select that and column data format as Text | finish | On Oct 17, 3:54*pm, edeaston wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
On Fri, 17 Oct 2008 03:54:08 -0700, edeaston
wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed First number: =--LEFT(A1,FIND(" ",A1)-1) Second number: =--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",RE PT(" ",99)),99)) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
Thank you to everyone who replied - all of the suggestions fit the bill
perfectly! Cheers Ed "Ron Rosenfeld" wrote: On Fri, 17 Oct 2008 03:54:08 -0700, edeaston wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed First number: =--LEFT(A1,FIND(" ",A1)-1) Second number: =--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",RE PT(" ",99)),99)) --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
Hi Mike,
I used this formula in my spreadsheet and this is the result I received for the following data: (How can I change the formula to account for the - (dash), which by the way is not always in the same place. I'm trying to eliminate the space and total from this data. 10007 10007 Total 10008 10008 Total 10012 10012 Total 10013 10013 Total #VALUE! 10014-3 Total #VALUE! 10018-3 Total -- Thanks in advance for your help. Tickfarmer "Mike H" wrote: Hi, For the first number =LEFT(A1,FIND(" ",A1))*1 and the second =MID(A1,FIND("(",A1)+1,(FIND(")",A1,1))-(FIND("(",A1,1)+1))*1 Drag both down for subsequent rows. Mike "edeaston" wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference only part of a cell
This formula will do it. Replace the A1 with your formula. You are taking
the left characters of your string and trimming off the Total part. =LEFT(A1,LEN(A1)-6) "Tickfarmer" wrote in message ... Hi Mike, I used this formula in my spreadsheet and this is the result I received for the following data: (How can I change the formula to account for the - (dash), which by the way is not always in the same place. I'm trying to eliminate the space and total from this data. 10007 10007 Total 10008 10008 Total 10012 10012 Total 10013 10013 Total #VALUE! 10014-3 Total #VALUE! 10018-3 Total -- Thanks in advance for your help. Tickfarmer "Mike H" wrote: Hi, For the first number =LEFT(A1,FIND(" ",A1))*1 and the second =MID(A1,FIND("(",A1)+1,(FIND(")",A1,1))-(FIND("(",A1,1)+1))*1 Drag both down for subsequent rows. Mike "edeaston" wrote: Hi, I have a column of data on one worksheet that contains two sets of numbers, with the second number in brackets (see below). This data is actually based on a mixture of underlying formulas for some cells which change month on month and some that are manually entered. 2 (4) 5 (5) 11 (22) 6 (9) On another worksheet I want to display just part of this data - the first numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the number could be single or double figures. Any ideas? Also, is it possible to display the second number on the same worksheet (different column) using your suggestions? Thanks in advance, let me know if you need further clarification. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference to a Variable Part of a Cell | Excel Worksheet Functions | |||
How to use number in some cell as a part of other cells reference? | Excel Worksheet Functions | |||
Using the result of formula as part of reference to a cell in form | Excel Worksheet Functions | |||
Using a cell reference as part of a link | Excel Discussion (Misc queries) | |||
worksheet tab name as part of a cell reference | Excel Worksheet Functions |