extract numbers from text or a constant

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
=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
 
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
 
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.
 
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
 
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
 
Thank you for your help. I copied all information into another sheet and
everything worked.
Thank you so much
 
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
 
Back
Top