Consider the simpler array formula
=MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))*N(MID(A1,R OW(INDIRECT("1:"&LEN
(A1))),1)=","))+1,1024)

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Absolutely fantastic, thanks very much

Thanks
Michael

"sk" wrote:

Assuming your records are housed in A1:A1200

A bit cumbersome but try -

=MID(A1,MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:" &LEN(A1))))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN( A1))))))+1,LEN(A1)-MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1) )))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))) )

Hit Ctrl+Shft+Enter

-sk

Michael wrote:
I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks