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

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

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

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 -

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 -

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 -

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 -