extract numbers from text or a constant

[Stephanie]

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

[Peo Sjoblom]

=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" <[email protected]> wrote in message
news:[email protected]...
>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

[Gary''s Student]

=--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

[Biff]

Hi!

Try this:

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

Biff

"Stephanie" <[email protected]> wrote in message
news:[email protected]...
>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

[Dkso]

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" <[email protected]> wrote in message
news:[email protected]...
>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

[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

[Peo Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
>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

[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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[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

[Peo Sjoblom]

You can't use format to change if excel thinks the cells are text, you need
to create a calculation
like doing an edit>replace 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 edit>replace,
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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>