Extract two separate totals from one column

[something68]

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

 

[Pete_UK]

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

 

[something68]

I'm afraid neither works. The results are # - pound signs.

 

[Pete_UK]

Widen those columns to see the values.

Pete

I'm afraid neither works.  The results are # - pound signs.





- Show quoted text -

 

[something68]

It show #VALUE!

Widen those columns to see the values.

Pete

 

[Rick Rothstein]

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.

 

[Pete_UK]

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