John, this is an excellent Solution, very clever.

I was trapped in a SEARCH LEFT SUBSTITUTE nightmare. I forgot all
about FIND.

Thank you very much.

John Bundy (remove) wrote:
Try this out, paste in and copy down

=MID(A1,FIND("-",A1)-2,14)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

I have some messy data which has more text in each cell than I need.
For Example:

CompanyName 01-01-118-02W6
Comp Name 01-01-118-02W5
UselessPrefix 01-01-119-02W6
01-02-118-02W6 Useless Suffix
A01-01-119-02W6 Company
01-01-118-02W6duplicate

I want to extract only the "##-##-###-##W#" portion from each cell
(these are surface locations for anyone curious). I want to eliminate
prefixes and suffixes of any type, and report nothing if the cell
doesn't contain the appropriately inputted string.

There are about 30000 records.

Any suggestions?