Extract two separate totals from one column

S

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
 
P

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
 
R

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top