text form to numeric ?

[crapit]

E.g A cell contain 1233- but the value is actually a numric value whereby
the "-" behind is a negative sign, wat function do I use to convert it to a
numeric form causing it to to become -1233 ?

 

[RWN]

One way, assuming that the values are mixed (some with/without "-" sign)
and that the values are in colA, which is formatted as text;

IF(RIGHT(Ax,1)="-",LEFT(Ax,LEN(Ax)-1)*-1,VALUE(Ax))

 

[crapit]

Assuming tat the cell to check is the same cell where the formula reside,
how do I stop the formula from disappearing? After entering a value on the
cell where it contain formula, it disappear.
I tried to use conditional formatting but failed

 

[Frank Kabel]

You have to enter the formula in a separate column. e.g. your source
data is in column A and the formula is in column B. Then enter Rob's
formula in B1 (substituting the x with 1 and copy this cell for all
rows).

You cannot enter data and a formula in the same cell.
Frank

 

[Ron Rosenfeld]

E.g A cell contain 1233- but the value is actually a numric value whereby
the "-" behind is a negative sign, wat function do I use to convert it to a
numeric form causing it to to become -1233 ?

If you have a later version of XL, you can use the Data/Text to Columns wizard.


--ron

 

[RWN]

I'd be interested to see how the text->column function would work in
this case, or any other simpler method for that matter. This is a
situation I run into daily dealing with files downloaded from the
mainframe that have trailing negative signs.

--
Regards;
Rob
------------------------------------------------------------------------

 

[Peo Sjoblom]

Select the column, do data>text to columns and click Finish
That's it. Trailing minuses become real negative numbers and regular
positive numbers stay the same

--

Regards,

Peo Sjoblom

I'd be interested to see how the text->column function would work in
this case, or any other simpler method for that matter. This is a
situation I run into daily dealing with files downloaded from the
mainframe that have trailing negative signs.

 

[RWN]

Peo;
Can't get it to work so it means I'm missing something.
Is this supposed to work w/XL2k?

--
Regards;
Rob
------------------------------------------------------------------------

Select the column, do data>text to columns and click Finish
That's it. Trailing minuses become real negative numbers and regular
positive numbers stay the same

--

Regards,

Peo Sjoblom

I'd be interested to see how the text->column function would work in
this case, or any other simpler method for that matter. This is a
situation I run into daily dealing with files downloaded from the
mainframe that have trailing negative signs.

--

 

[Peo Sjoblom]

Nope, it was introduced with 2002.

 

[RWN]

Alas, always behind the times!

Thanks.

--
Regards;
Rob
------------------------------------------------------------------------

Nope, it was introduced with 2002.

--

Regards,

Peo Sjoblom

Peo;
Can't get it to work so it means I'm missing something.
Is this supposed to work w/XL2k?

--

Select the column, do data>text to columns and click Finish
That's it. Trailing minuses become real negative numbers and regular
positive numbers stay the same

--

Regards,

Peo Sjoblom

I'd be interested to see how the text->column function would work in
this case, or any other simpler method for that matter. This is a
situation I run into daily dealing with files downloaded from the
mainframe that have trailing negative signs.

----------------------------------------------------------------------

wrote:

E.g A cell contain 1233- but the value is actually a numric value
whereby
the "-" behind is a negative sign, wat function do I use to convert
it to a
numeric form causing it to to become -1233 ?


If you have a later version of XL, you can use the Data/Text to
Columns wizard.


--ron