format text as number
 

I am trying to convert celss which include both numbers and text, which I
have imported. (I think from a LOTUS 123 programme) I have used both
'REPLACE' and SUBSTITUTE' but the number remaining are still treated as text
and will not accept formats. Please Someone...HELP. Thx Ash
--
Asheret

format text as number
 

VALUE
See Also

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Text is the text enclosed in quotation marks or a reference to a cell
containing the text you want to convert. Text can be in any of the constant
number, date, or time formats recognized by Microsoft Excel. If text is not
in one of these formats, VALUE returns the #VALUE! error value.

Remark

You do not generally need to use the VALUE function in a formula because
Microsoft Excel automatically converts text to numbers as necessary. This
function is provided for compatibility with other spreadsheet programs.

Examples

VALUE("$1,000") equals 1,000

VALUE("16:48:00")-VALUE("12:00:00") equals "16:48:00"-"12:00:00" equals 0.2,
the serial number equivalent to 4 hours and 48 minutes.



Mike F

"Asheret" wrote in message
...
I am trying to convert celss which include both numbers and text, which I
have imported. (I think from a LOTUS 123 programme) I have used both
'REPLACE' and SUBSTITUTE' but the number remaining are still treated as
text
and will not accept formats. Please Someone...HELP. Thx Ash
--
Asheret

format text as number
 

On May 3, 3:09 am, Asheret wrote:
I am trying to convert celss which include both numbers and text, which I
have imported. (I think from a LOTUS 123 programme) I have used both
'REPLACE' and SUBSTITUTE' but the number remaining are still treated as text
and will not accept formats. Please Someone...HELP. Thx Ash
--
Asheret


Are you trying to do this just one time to get the data as a number?
Or are you trying to create a VBA routine to accomplish this task?

If it is just a one time need, then you should be able to highlight
the cells and then format them as a number. (Although, I tried this
by entering forced text into some cells and it didn't work to convert
that cell's format to a number.)

Again for a one time event, here is what the HELP system recommends
(the topic title was - "Convert numbers stored as text to numbers"):
----------------
Convert numbers stored as text to numbers
One cell at a time

On the Tools menu, click Options, and then click the Error Checking
tab.
Make sure the Enable background error checking and Number stored as
text boxes are checked.
Select any cell with a green error indicator in the upper left
corner .
Next to the cell, click the button that appears , and then click
Convert to Number.
A whole range at once

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.
--------------

Finally, if you are doing this in a VBA program, you can use the
CINT() function:

var2 = cint(.Range("A1"))

or

var 2 = cint(varstring)

There are probably more elegant ways to do this. I am just learning
VBA myself so my knowledge is limited. I hope these help.