Spltting the address in one cell to multiple cells

Hi Shazad

Thank you for your question. I think it will be tricky to find a way for Excel to split your data in a meaningful way.

It is possible for Excel to split the contents of a cell. To do so it needs a pattern so that it knows where to break the text into its respective components.

For example:

First Last Postcode

Could be split into three values using the space as a separator. Even if there were no space Excel could perhaps look for a capital letter as the sign that a break in the data is required

FirstLastPostcode

Unfortunately it is hard to see the pattern that Excel could use in the data you need to split.

FIRSTLASTPOSTCODE

As the spaces are erratic and all the text is uppercase.

Perhaps a practical answer would be to edit the data, but rather than manually put in spaces as a separator you may wish to try using a symbol such as #

Edit each record adding a # character where the break should be.

Then use Data > Text to Columns to split your cell values for you.
The wizard that appears will ask if your data is delimited (which it is, the # is the delimeter)
After clicking "next" you will be able tell Excel to use the # symbol to create each break.
The last step of the wizard lets you format the data as text or date (if needed) then click Finish.

At each # symbol Excel will split the data and move each value into a new cell

I'm sorry I couldn't offer you a more comprehensive solution. Although using the # symbol requires some manual editing. Combining with the Text to columns wizard saves you from having to do lots of cut and pasting which is fiddly.


Kind regards,
Andrew