Why is it not a number???

[magicool]

Hi,
I am using SUBSTITUTE to get only the number from a cell. What am I doing wrong? Please check the attach!

Thank you!

[zbor]

Here, try this:

=--SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","");",",".")


Also: what's your number delimiter?

For me this formula will give error because =--79.64 is not number to me.
If you have same settings you'll get error too.

In that case
=--LEFT(A1;FIND(" ";A1)-1)

is enough to get your prices out

[martindwilson]

there are char(160) in there if you do len(c1) youll see that its 8 char long
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((A2),".",""),",","."),"EUR",""),CHAR(160),"")

[magicool]

Thank You! It works!!!
Is it possible to include the SUM in this formula? (so I will not add another column and get the total directly in A23)

[martindwilson]

i hope you used the revise function above as i forgot to replace the .

[magicool]

Sure. I noticed

In this case is it possible to add =SUM(SUMIF(A1:A2000,{"<0",">0"})) to the formula?

[martindwilson]

as i said in your other post clean up the data first
in this instance data/text to columns select fixed width
click next
click just to the left of eur to insert a break
click next
click where it says general just above EUR
check the do not import column(skip button)
then find replace comma with period
and whats this
=SUM(SUMIF(A1:A2000,{"<0",">0"})) supposed to be doing?

[magicool]

All solved. Thank You!