I have cell with NW123456 in p4. I am using the following to remove the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate a
constant.

Thank you for any help.
--
Stephanie

=IF( LEFT(P4,2)="NW", VALUE(MID(P4,3,255)))

so NW is the only option?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Stephanie" wrote in message
...
I have cell with NW123456 in p4. I am using the following to remove the
nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate
a
constant.

Thank you for any help.
--
Stephanie

=--RIGHT(P4,LEN(P4)-2)

will work
--
Gary's Student


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate a
constant.

Thank you for any help.
--
Stephanie

Hi!

Try this:

=IF(LEFT(P4,2)="nw",--MID(P4,3,255),"")

Biff

"Stephanie" wrote in message
...
I have cell with NW123456 in p4. I am using the following to remove the
nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate
a
constant.

Thank you for any help.
--
Stephanie

If the contents in the cell are not constant (the same length all the time)
try this...
=IF(LEFT(P4,2)="nw",RIGHT(P4,LEN(P4)-2))
This does not account for the cell not starting with "NW" and for some
reason (I'm not sure why) but it's not case specific! it does however take
the length of the contents into account!

Dean
"Stephanie" wrote in message
...
I have cell with NW123456 in p4. I am using the following to remove the
nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate
a
constant.

Thank you for any help.
--
Stephanie

I typed in =if right(p4,len(p4)-2). I was not sure what the dashs meant.
However the formula simply sits in the cell. It does not go away and reflect
an answer. The formula editor continues to state that the value in p4 is a
constant. All of the step thru etc in the formula editor is grayed out.
I also tried the following solution =IF( LEFT(P4,2)="NW",
VALUE(MID(P4,3,255))). With the same results.. the formula sits in the cell
with out results

Do the cell contents in P4 need to be changed to something else. I am not
sure why it is calling it a constant. It should read as text.

--
Stephanie


"Gary''s Student" wrote:

=--RIGHT(P4,LEN(P4)-2)

will work
--
Gary's Student


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate a
constant.

Thank you for any help.
--
Stephanie

Make sure the cell with the formula is not formatted as text, all the
solutions you got will work



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Stephanie" wrote in message
...
I typed in =if right(p4,len(p4)-2). I was not sure what the dashs meant.
However the formula simply sits in the cell. It does not go away and
reflect
an answer. The formula editor continues to state that the value in p4 is
a
constant. All of the step thru etc in the formula editor is grayed out.
I also tried the following solution =IF( LEFT(P4,2)="NW",
VALUE(MID(P4,3,255))). With the same results.. the formula sits in the
cell
with out results

Do the cell contents in P4 need to be changed to something else. I am not
sure why it is calling it a constant. It should read as text.

--
Stephanie


"Gary''s Student" wrote:

=--RIGHT(P4,LEN(P4)-2)

will work
--
Gary's Student


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove
the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to
evaluate a
constant.

Thank you for any help.
--
Stephanie

I use the Format to change the entire column into the number format. I have
just discovered that I cannot add = 1+1. This is not my spreadsheet and
have never run into something of this nature. Should I copy what I need into
another sheet
Thanks for your help
--
Stephanie


"Peo Sjoblom" wrote:

Make sure the cell with the formula is not formatted as text, all the
solutions you got will work



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Stephanie" wrote in message
...
I typed in =if right(p4,len(p4)-2). I was not sure what the dashs meant.
However the formula simply sits in the cell. It does not go away and
reflect
an answer. The formula editor continues to state that the value in p4 is
a
constant. All of the step thru etc in the formula editor is grayed out.
I also tried the following solution =IF( LEFT(P4,2)="NW",
VALUE(MID(P4,3,255))). With the same results.. the formula sits in the
cell
with out results

Do the cell contents in P4 need to be changed to something else. I am not
sure why it is calling it a constant. It should read as text.

--
Stephanie


"Gary''s Student" wrote:

=--RIGHT(P4,LEN(P4)-2)

will work
--
Gary's Student


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove
the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to
evaluate a
constant.

Thank you for any help.
--
Stephanie

Thank you for your help. I copied all information into another sheet and
everything worked.
Thank you so much

--
Stephanie


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to evaluate a
constant.

Thank you for any help.
--
Stephanie

You can't use format to change if excel thinks the cells are text, you need
to create a calculation
like doing an editreplace and replace = with = what it basically does it
will trigger a calculation in the text string formulas thus making them work
like formulas again, so format all cells as general and do editreplace,
find what =
replace with =

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Stephanie" wrote in message
...
I use the Format to change the entire column into the number format. I
have
just discovered that I cannot add = 1+1. This is not my spreadsheet and
have never run into something of this nature. Should I copy what I need
into
another sheet
Thanks for your help
--
Stephanie


"Peo Sjoblom" wrote:

Make sure the cell with the formula is not formatted as text, all the
solutions you got will work



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Stephanie" wrote in message
...
I typed in =if right(p4,len(p4)-2). I was not sure what the dashs
meant.
However the formula simply sits in the cell. It does not go away and
reflect
an answer. The formula editor continues to state that the value in p4
is
a
constant. All of the step thru etc in the formula editor is grayed
out.
I also tried the following solution =IF( LEFT(P4,2)="NW",
VALUE(MID(P4,3,255))). With the same results.. the formula sits in the
cell
with out results

Do the cell contents in P4 need to be changed to something else. I am
not
sure why it is calling it a constant. It should read as text.

--
Stephanie


"Gary''s Student" wrote:

=--RIGHT(P4,LEN(P4)-2)

will work
--
Gary's Student


"Stephanie" wrote:

I have cell with NW123456 in p4. I am using the following to remove
the nw.

=if left(p4,2)="NW" or "nw", Value(left(p4)-2)

But it does not work. The formula auditor states I am trying to
evaluate a
constant.

Thank you for any help.
--
Stephanie