Cherry has attended:
Excel Intermediate course
Excel Advanced course
Formula for separating data in one box into several boxes.
Hi, I would be grateful if you knew of a formula that could separate parts of an address in one box into e.g. 4 boxes. I know there is the Concatenate formula for doing the opposite to put data in 4 fields into 1 field but is there a formula to do the opposite?
Many thanks
Cherry
RE: formula for separating data in one box into several boxes.
Hi Cherry,
Thank you for your question and welcome to the forum.
If you have an address string in one cell with each word separated by a comma, such as address,town,county,postcode, then you can use the Convert to Text command.
If you insert three extra columns to the right of the column that contains the text string. Now select the cell containing the text string. Go to Data, Convert Text to Columns and choose Delimeted option in screen 1. In the second screen place a tick in the appropriate checkbox that tells Excel how each part of the address is separated(e.g. comma, space, tab etc.). Then click finish.
The data shoud be separated into the four columns with the first column containing the Address and then the other three containing the town, county and postcode.
I hope this answers your question.
Regards
Simon
