Extracting last characters from a cell.

I have about 1000 addresses in this format:

Dysart Road
Grantham
Lincolnshire
NG31 7AA

They are all in one single cell, with Alt Enter separating each line of the address. Does anyone have any advice on how I can pull out the post code, which comes after after the final "Alt Enter" in every cell.

The number of rows in the cell is different, and the last six letters doesn't work because obviously the number of characters in the postcode varies.

Will love anyone who can solve this!

Thanks!

try this (array formula)
{REPLACE(A1,1,MAX(IF(MID(A1,ROW(1:100),1)=" ",ROW(1:100))),"")}

Thanks guys, none of those methods worked however in the end i found this as a solution:

http://www.accountingweb.com/topic/t...-columns-excel

Not as beautiful as your efforts though

if that you mean, so try this option (array formula)
{=REPLACE(A1,1,MAX(IF(MID(A1,ROW(1:100),1)=CHAR(10),ROW(1:100))),"")}