CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Hammad
Hammad

Reputation: 31

Excel 2013: convert text to number

I have one column where I've pasted values such as '$‎1,995.00'. I want to use this value and make calculations on it.

Here's what I tried in the column next to my data:

=RIGHT(L10,8)+0

This works. BUT the number of integers in my data cells varies. So when I try =RIGHT(L10,LEN(L10)-1)+0, it doesn't work. And I don't understand why.

There has to be a simpler way of doing this.

Upvotes: 2

Views: 142

Answers (2)

user4039065
user4039065

Reputation:

The first 'tick' (aka single quote or CHAR(39)) is being interpreted as a Range.PrefixCharacter property. This commonly forces numbers to text and is not displayed. Conversion to a true number will also be hampered by the currency symbol but not by the comma.

For a more universal conversion try one of these,

=VALUE(MID(L10, 3, LEN(L10)-3))
=--MID(L10, 3, LEN(L10)-3)
=MID(L10, 3, LEN(L10)-3)*1
=MID(L10, 3, LEN(L10)-3)+0

Upvotes: 1

PankajR
PankajR

Reputation: 407

Try

=SUBSTITUTE(SUBSTITUTE(L10,"$",""),",","")+0

This will get rid of '$' & ',' and +0 will convert the resulting to integer/number

Upvotes: 1

Related Questions