Help to Split address

[Paul at GTS]

Hi, I have a existing data file that contains address a whole address in one cell :

Mr Smith
9 The Steet
LONDON
UK

I need to now split each line into seprate cells:

A1 - Mr Smith
A2 - 9 The Street
A3 - LONDON
A4 - UK

Any ideas ?

Using Office 365 Excel 1708 Build 8431.2079

Thanks

 

[admiral100]

Hi,

If between the lines there is an Enter you can try this : (A1 = long address)

B1 =TRIM(MID(SUBSTITUTE(A$1,CHAR(10),REPT(" ",1000)),ROW(A1)*1000-999,1000))
and drag down

 

[Paul at GTS]

Thanks admiral100

Works a treat

 

[admiral100]

Thanks for the feedback