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?

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?