How do I convert text to number

Posted by Mark Alewine on February 15, 2000 2:56 PM

I copied some data from the web into a spreadsheet. Problem is that dates and numbers are displayed as text. I've tried pasting special...multiply by 1 that is genreal formatted. I've also tried converting text to columns. Any ideas on how to convert the dates and numbers without re-entering everything?

Posted by billyjoeBob on February 16, 2000 12:06 PM

Use VALUE function.
Format column B for date:

Cell A1:
10/10/2010
Cell B1:
=value("A1")

This converts text to number assuming text can be represented numerically.

If data is jumbled (such as EURO date structure), use:

Cell A1:
20/10/2010
Cell B1:
=DATE(MID(A1,7,4),MID(A1,4,2),MID(A1,1,2))

Then (of course) drap the formula.

You may have to imbed an additional IF within the first argument of te MID statement for years having only two digits.

I prefer MID to LEFT/RIGHT functions because of the implicit nature of MID.