extract value from cell...using formulas

[monika]

can i extract 80 from P-TRED-80-1 using formulas?

the cases can be P-TRED-80-1, PG-EEEEE-144-1, A-SSS-20-13.
I can do this by logic. But wanted formula to be seen in
the cell. So how can i extract the value between 2dn and
3rd hyphen '-'

thanks

 

[Bob Phillips]

Hi Monika,

Kludgy, but ...

=MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,FIND("-",A1,FIND("-",A1,FIND("-",A1)+1
)+1)-FIND("-",A1,FIND("-",A1)+1)-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

You need to use the SEARCH Function in nested Formulas.
Here's how to build it. In Cell "B1" type PG-EEEEE-144-1

In Cell "B2" formula =SEARCH("-",B1,1)
Result = 3

In Cell "B3" formula =SEARCH("-",B1,SEARCH("-",B1,1)+1)
Result = 9

In Cell "B4" formula =SEARCH("-",B1,SEARCH("-",B1,SEARCH("-
",B1,1)+1)+1)
Result = 13

In Cell "B5" formula =MID(B1,B3+1,B4-B3-1)
Result = 144 This is Text

To convert to a number
In Cell "B6" formula =VALUE(MID(B1,B3+1,B4-B3-1))
Result = 144 This is a number

Now nest all the formulas together

To return a text value
=MID(B1,SEARCH("-",B1,SEARCH("-",B1,1)+1)+1,SEARCH("-
",B1,SEARCH("-",B1,SEARCH("-",B1,1)+1)+1)-SEARCH("-
",B1,SEARCH("-",B1,1)+1)-1)

To return a number
=VALUE(MID(B1,SEARCH("-",B1,SEARCH("-",B1,1)+1)+1,SEARCH("-
",B1,SEARCH("-",B1,SEARCH("-",B1,1)+1)+1)-SEARCH("-
",B1,SEARCH("-",B1,1)+1)-1))

Peter Scott

 

[gitcypher]

conditional formatting

see the attached workshee

Attachment filename: conditional formating.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49540