| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract two separate totals from one column
In Column A, row 8 - I show: A, S
In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
Put this in M9:
=LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44*pm, something68 wrote: In Column A, row 8 - I show: *A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
I'm afraid neither works. The results are # - pound signs.
"Pete_UK" wrote: Put this in M9: =LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44 pm, something68 wrote: In Column A, row 8 - I show: A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
Widen those columns to see the values.
Pete On Sep 14, 2:15*pm, something68 wrote: I'm afraid neither works. *The results are # - pound signs. "Pete_UK" wrote: Put this in M9: =LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44 pm, something68 wrote: In Column A, row 8 - I show: *A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
It show #VALUE!
"Pete_UK" wrote: Widen those columns to see the values. Pete On Sep 14, 2:15 pm, something68 wrote: I'm afraid neither works. The results are # - pound signs. "Pete_UK" wrote: Put this in M9: =LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44 pm, something68 wrote: In Column A, row 8 - I show: A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
Your "1.0, 7.0" entry (without the quotes) is located in the single cell A9,
right? And the entry has a comma separating the two values, right? As long as both of these conditions are met, Pete's formulas work fine for me. -- Rick (MVP - Excel) "something68" wrote in message ... It show #VALUE! "Pete_UK" wrote: Widen those columns to see the values. Pete On Sep 14, 2:15 pm, something68 wrote: I'm afraid neither works. The results are # - pound signs. "Pete_UK" wrote: Put this in M9: =LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44 pm, something68 wrote: In Column A, row 8 - I show: A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two separate totals from one column
I think you may have some other non-visible characters in A9 - the
most common is a non-breaking space character which has a code of 160. Modify the formulae as below: =--SUBSTITUTE(LEFT(A9,SEARCH(",",A9)-1),CHAR(160),"") and: =--SUBSTITUTE(RIGHT(A9,LEN(A9)-SEARCH(",",A9)),CHAR(160),"") Hope this helps. Pete On Sep 14, 3:23*pm, something68 wrote: It show #VALUE! "Pete_UK" wrote: Widen those columns to see the values. Pete On Sep 14, 2:15 pm, something68 wrote: I'm afraid neither works. *The results are # - pound signs. "Pete_UK" wrote: Put this in M9: =LEFT(A9,SEARCH(",",A9)-1) and this in O9: =RIGHT(A9,LEN(A9)-SEARCH(",",A9)) These will be text values, but if you want them to be numbers you can do this: =--LEFT(A9,SEARCH(",",A9)-1) and: =--RIGHT(A9,LEN(A9)-SEARCH(",",A9)) Hope this helps. Pete On Sep 14, 1:44 pm, something68 wrote: In Column A, row 8 - I show: *A, S In Column A, row 9 - I show: 1.0, 7.0 I would like Column M, row 9 to extract 1 from column a, row 9 I would like Column O, row 9 to extract 7 from column a, row 9- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Two totals in one cell shown in two separate columns | Excel Discussion (Misc queries) | |||
| How to extract data from one column into two separate columns | Excel Worksheet Functions | |||
| Sorting Existing Totals Into A New & Separate Column | Excel Worksheet Functions | |||
| Summing totals on separate worksheet based on 2 criteria | Excel Discussion (Misc queries) | |||
| Formula for adding totals from separate worksheets | Excel Discussion (Misc queries) | |||
Hybrid Mode
