Splitting data from cells (examples included)

Hello

Firstly, apologies if I have posted in the wrong sub-forum; I am relatively new to Excel and more so the forums.

My friend has asked me to assist him in sorting his data/spreadsheets but I am somewhat stuck at this particular moment in time.

I am using Excel 2010.

I have attached two files; the first file entitled 'Template for data that requires sorting.xlsx', and then what I hope will be the result, entitled 'Template for data result.xlsx'.

I would very much appreciate any help on this matter, as I have an awful lot of data that can hopefully be sorted in the aforementioned format.

If you require any further information, please ask.

Many thanks,
Mark

Without delimiters in "ClientAddress", I'm afraid you'll have to do this manually.

How can excel tell where to split the string?
e.g. you need this, split at the commas.
1 Green Park Lane, Steeton, Keighley, West Yorkshire, BD24 7RE
How can we tell that this is not correct?
1 Green Park, Lane Steeton, Keighley West, Yorkshire, BD24 7RE
Extra spaces don't help either!

- Select the column < telephone >
- Text to Columns
- Choose your delimited "SPACE"

Text to Columns will only work if you add extra column after the column you need to split, you can use the destination option, but this will not allow you to choose an address on a different sheet.

With your datasheets clear all formatting first, this will make life easier.
Your destination sheet is formatted for text, if this isn't cleared any formula you use will show as text and not calculate.

See the formula in A2 This can be dragged across and down as required, I have added an extra column, Column D, to allow for the possiblity that the original name has more than 3 parts.

The formula in E2, should remove the Post Code from the "ClientAddress", assuming it is always in 2 parts.
Then in I2 we can extract the Post Code.

J2 Across and Down works in a similar way to A2.

You can then copy the lot and paste special > values for further editing.

This is not a bullet proof solution, just something to show you some ways to manipulate data strings, and perhaps also reduce some of the manual editing you will have to do.