Convert Text Field to Currency

[PJinMK1]

Can anybody advise how I can convert what seems to be a text field into a currency field?

I have a report which I have exported from a website but whatever I do I cannot convert this field into anything that I can subsequently use to run calculations on?

Can anybody help?

Thanks

 

[Joe4]

Welcome to the Board!

Please post some samples of how exactly the data is appearing.

 

[PJinMK1]

As a new member I don't seem to be able to attach any files but hopefully the below shows you what it looks like. Its the value column which I'm having the issues with. It looks normal but I think its a text field as when I evaulate a formula against this field the value seems to be in quotation marks.

[TABLE="width: 785"]
<colgroup><col><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]PlayerName[/TD]
[TD]Date&Player[/TD]
[TD]Value[/TD]
[TD]MatchdayScore[/TD]
[TD]MediaScore[/TD]
[TD]MatchdayDividends[/TD]
[TD]MediaDividends[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Neymar[/TD]
[TD]43506Neymar[/TD]
[TD]£20.12[/TD]
[TD][/TD]
[TD]310[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Paul Pogba[/TD]
[TD]43506Paul Pogba[/TD]
[TD]£20.06[/TD]
[TD][/TD]
[TD]260[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Kylian Mbappé[/TD]
[TD]43506Kylian Mbappé[/TD]
[TD]£16.52[/TD]
[TD][/TD]
[TD]260[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Lionel Messi[/TD]
[TD]43506Lionel Messi[/TD]
[TD]£15.64[/TD]
[TD]117[/TD]
[TD]180[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Eden Hazard[/TD]
[TD]43506Eden Hazard[/TD]
[TD]£14.03[/TD]
[TD]13[/TD]
[TD]110[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Mohamed Salah[/TD]
[TD]43506Mohamed Salah[/TD]
[TD]£13.93[/TD]
[TD][/TD]
[TD]170[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Marcus Rashford[/TD]
[TD]43506Marcus Rashford[/TD]
[TD]£13.75[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Jadon Sancho[/TD]
[TD]43506Jadon Sancho[/TD]
[TD]£12.41[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Vinicius Junior[/TD]
[TD]43506Vinicius Junior[/TD]
[TD]£11.90[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Ousmane Dembele[/TD]
[TD]43506Ousmane Dembele[/TD]
[TD]£10.16[/TD]
[TD]14[/TD]
[TD]100[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Harry Kane[/TD]
[TD]43506Harry Kane[/TD]
[TD]£10.16[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Cristiano Ronaldo[/TD]
[TD]43506Cristiano Ronaldo[/TD]
[TD]£9.12[/TD]
[TD]151[/TD]
[TD]380[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]10/02/2019[/TD]
[TD]Anthony Martial[/TD]
[TD]43506Anthony Martial[/TD]
[TD]£8.20[/TD]
[TD][/TD]
[TD]220[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

So let's say the first value of £20.12 is in cell D2.

What is the result of the formula
=istext(d2)
?

 

[PJinMK1]

This comes back with a TRUE statement

 

[Gerald Higgins]

OK, and what is the result of this formula ?
=LEN(D2)

 

[PJinMK1]

6

 

[Gerald Higgins]

OK, so you have a text field, that looks like a number, and the LEN function tells us that there are no hidden characters, which is good.

In my version of Excel, when I enter
£20.12
as a text string, I can still use this in formulas, for example
=D2+2
returns
22.12

What exactly are you trying to do with this value, and in what way is it not working ?

 

[PJinMK1]

I am doing similar I am referencing this cell with a basic +D2-0.01 but it just returns a #VALUE response

 

[GR00007]

You might just need to strip the leading £ if it exists. This will take the value of the cell if it is numeric or text, next text will be changed to numeric with the leading character stripped, then if there's still an error zero will be returned:
=IFERROR(VALUE(D2),IFERROR(VALUE(MID(D2,2,30)),0))