Cannot get VALUE function to work with cell references

[Michael Plog]

I have a spreadsheet that has numbers entered as text. (I don't know
why they did it!) I want to convert these to numeric values so I can
sort properly, add, etc. When I use =value(L2) I get an error. Can
anyone help?

Thanks,
Michael Plog
(e-mail address removed)

 

[Franz Verga]

I have a spreadsheet that has numbers entered as text. (I don't know
why they did it!) I want to convert these to numeric values so I can
sort properly, add, etc. When I use =value(L2) I get an error. Can
anyone help?

Thanks,
Michael Plog
(e-mail address removed)

Hi Michael,

Try with:

=value(trim(clean(L2)))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Michael Plog]

Franz,
Thanks for the advice. I checked out the functions and this formula
did not work. The following, however, did:
=value(left(l2,len(l2)-1))

I think what happened is when they entered data into the system, it
comes in as text, then gets a space put behind it to go into the table
that builds the Excel spreadsheet. Anyway, I was trying (as possibly
you were) for an equal sign or unprintable character. Since the space
is printable, AND doesn't show depending on position within cell, I
was off. I was also spending a lot of time on the RIGHT() function
instead of the LEFT.

Again, thanks--you got me pointed in the right direction.