Thanks Rick.

I want a formula next to the old list which will return the new identifier.
This will allow me to keep the old data despite moving to a newer reproting
system.

These formulas look good. I'll play about with them - hopefully one will do
the job.

Cheers!

Tom.

"Rick Rothstein (MVP - VB)" wrote:

So, I need a formula which I can put in next to the old list (123a etc)
which looks for the staff id in the list of new unique tags
(wm-bath-jsmith-123a would be a match).

It should only match values after the final hyphen.

I'm not exactly sure what you are looking for here (Formula next to your old
list? That will return what?). Anyway, maybe you can make use of one of
these...

Get staff ID from new list entry
(assuming number of dashes can vary)
**********************************
=MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255)
where A1 has the new staff ID

Get staff ID if there are always 3 dashes
**********************************
=MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",3))+1,255)
where A1 has the new staff ID

See if old staff ID is in new staff ID
**********************************
=NOT(ISERROR(SEARCH(C1,A1)))
where C1 has old staff ID, A1 has new staff ID


Rick