convert text to number with a formula

[HeyInKy]

This is probably simple, but I'm stuck. I need a formula to copy a number stored as a text to the number.

I have a formula in, let's say on Sheet1, cell A1: =IF($M$3,TEXT(N7,"0%"),TEXT(N7,"0.0%"))

I then copy this cell to another sheet, let's say Sheet2, cell A1 =Sheet1!A1. I have then have a bar chart linked to this cell, but it's reading it as text and no bars are appearing in the chart - even though the cell in the second sheet is formatted as a number!

FYI - my work around is to link my chart to the underlying data in the formula above, in this case, N7. But then I'm not getting the whole number (%) or decimal (%) that I want when the value is displayed in the chart.

Thanks.

-HeyInKy

[Jonmo1]

The TEXT function returns...well... TEXT
Even if it looks like a number.

You can use +0 after the text function to convert it to a real number.

=IF($M$3,TEXT(N7,"0%"),TEXT(N7,"0.0%"))+0

[HeyInKy]

Excellent!! Thank you!!

[Jonmo1]

You're welcome.