Splitting content in separate columns

Hi. I am trying to separate an address in to separate columns, which at the momemnt has been wrapped so using the Text to columns is not working.

I have used this formula - =RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))
which has worked so far in that is has separated each new part away from what went before but it still takes with it what follows it.

For example.

CELL A1 - Address 1, Address 2, City, Postcode
CELL A2 - Address 2, City, Postcode
CELL A3 - City, Postcode
CELL A4 - Postcode

How do I make it so that its:

CELL A1 - Address 1
CELL A2 - Address 2
CELL A3 - City
CELL A4 - Postcode

I'm guessing that it is going to be a slight variation on the formula I have already used. If you have any ideas I would really appreciate your help.

Thanks,

Jen

Example Excel Doc.xlsxThanks. I've created a basic spreadsheet and included the formula that I mentioned above. I would like to have each o the entries in the full address to be in a separate column. I hope you'll be able to help.
Thanks.

Thank you but it doesn't seem to work when I transfer it to the real data. It ended up cutting off the beginning of the address... making what should have been 12 Redwood Road - ood Road.

---------- Post added at 09:14 AM ---------- Previous post was at 09:08 AM ----------

It has worked perfectly for the other fields but Address 1 is being cut in half and missing the first part of it each time... the rest of the formulas are working though, I think it might be nearly there...

Amazing. Thank you so much. That has worked - you've just saved me hours of trawling through and separating each one by hand!

---------- Post added at 10:01 AM ---------- Previous post was at 09:19 AM ----------

I'm still having a slightly problem with this. I am trying to transfer this through to Mail Merge in Word and although it is picking up the fields and the entries but each entry is being followed by _x000D_ how do I stop this from happening?

Thanks in advance.

No it doesn't show up in the excel document but it only shows up in Word in merge for the entries that are there as a result of the formula. For example it isn't appearing following the Mailing name which has just been inputted as normal text into the excel doc.

I will have a look on ome mail merge forums and see if I can get an answer there...

Hello MarvinP,

Unfortunately, my data is much more messier than Jen2412 and I really hope you can help me. I have cells that contain different/multiple lines and I had to split it for report purpose.

Kindly see my attachment, cell-C9.. I would like to have "2nd Line of Column-A" to be blank if the original data consist of only 1 line.. Same result as in "3rd Line of Column-A" and "4th Line of Column-A".

Hope you understand what I'm trying to explain.

Rgds,
CSMiin