Place betting profit/loss in excel

[Dai_Young]

Does anyone have an idea how I can get excel to recognise the profit/loss column output for place markets?

In excel it shows as (for example):

-£25.60,£9.59

The above is in a single cell. Excel can tell that this is greater than zero (which helps somewhat) but cannot read the amounts.


Any help greatly appreciated

[Ian]

Put the following formulas in the next two columns

=LEFT(U5,FIND(",",U5)-1)*1

=RIGHT(U5,LEN(U5)-FIND(",",U5))*1

[Dai_Young]

Excellent. Thanks for your help Ian.

[Dai_Young]

Ah, spoke too soon. Doesn't seem to work.

I think it is the £ sign that is messing things up. The formulae you suggested produced VALUE errors. I tried altering the formulae but I couldn't get excel to extract what it recognised as a numerical value. Also tried changing the formatting of the relevant cells to currency but that didn't work either.

Any other suggestions?

[Uncle Ernie]

You tried using substitute to remove the £ sign ?
i.e.

=SUBSTITUTE(A1, "£", "")




=RIGHT(SUBSTITUTE(A1, "£", ""),LEN(SUBSTITUTE(A1, "£", ""))-FIND(",",SUBSTITUTE(A1, "£", "")))*1

Above works OK might be an easier way of writing it though

[Ian]

Strange the formulas both work fine for me.

[Dai_Young]

Something strange indeed! I used the substitute method above and that gave me a recognisable output but it still had a £ sign in front of it

Maybe it's something to do with my regional settings. Anyway it works now, thanks for your help both.

[Captain Sensible]

The pound sign is probably there because the cells have been formatted to display as currency rather than just a number