Excel Tip > Excel Functions List > Excel Text Formulas > Splitting a full address into three or more separate cells in Microsoft Excel

Splitting a full address into three or more separate cells in Microsoft Excel

Fb-Button

Original Question:-

How to split a cell into 3 different cells?

I am importing text file in Excel, but all data is importing in a column I want to split a column data into 3 different cells. How can I do it please suggest.

In this thread user wants to split a cell into 3 and more cells as per data requirement. To fulfill this requirement we use "Text to Column" option.

Text to Columns: - This function is used to split a single column of text into multiple columns.

Let’s take an example and understand how we can use this function to split a single cell.

We have a list of addressesin column A which contains the entire address in a single cell. We needto split the address into separate columns.

 

image 1

 

There are 3 steps in the Text to Columns function:-

    • Select the column A.
    • Go to the “Data” tab, from the “Data Tools” group, click on “Text to Columns”.
    • “Convert Text to Columns Wizard – Step 1 of 3” dialog box will appear.
    • In the dialog box you will find 2 file types: - Delimited and Fixed Width.
      A. Delimited:-Character such as commas or tabs separate each field.
      B. Fixed Width: - Fields are aligned in columns with spaces between each field.

image 2

img3

  • For this data we have to select the Delimited option, because there is no fixed width.
  • Click on the Next button.
  • The “Convert Text to Column Wizard – Step 2 of 3” dialog box will appear.
  • Click and put a tick on the “Space” check box because our data delimiter is “Space”. When you will click on it, then you can see the data being separated in the data

preview box. If your data has comma as the delimiter which is mostly the case, you can select the comma check box.

 

img4

 

    • Click on the Next button.
    • The “Convert Text to Column Wizard – Step 3 of 3” dialog box will appear.

 

img5

 

  • Click on destination to choose the location where you want to split the text. The destination box will show $A$1 but if you do not want to over-write the existing data, then you can either select $B$1:$D$1 or just $B$1.
  • Click on the “Finish” button

 

img6

 

You can see above that the text from one cell in column A has been split into the column B:F. This is very useful when you receive the data as in column A and need to split it into multiple columns for further analysis and reporting.

Note:- If you merge data in Excel then will cell will not be split  in other cells through Text to Column.

Previous
Next

Comments

  1. Tony Williams (MBCC)

    DOESNT WORK NEED THE BELOW TO BE BROKEN INTO SEPARATE COLUMNS SO THE DATA CAN BE FED INTO AN ONLINE TOOL IN EXCEL /CSV

    EXAMPLE BELOW

    Cardiff Isuzu Ute,
    Cnr Harrison St & Myall Rd,
    Cardiff, NSW, 2285,
    02 4904 6777

    NAME ADDRESS SUBURB STATE POST CODE PHONE CONTACT
    Aspley Chrysler Jeep Dodge 1387 Gympie Road, Aspley QLD 4034 07 3633 3900 MATT

    Reply ↓
  4. T.R.

    Previous comments spot on. Data was rigged for EASY, not a practical answer . Addresses lack spaces in street name, "AppleTreeLane". Two name states are missing the spaces. Come on!

    Reply ↓
  5. Matt Foster

    Thanks for sharing this. We have created a solution that allows for more margin of errors within the data. Here is a link to a video on how it works.
    http://fosteringllc.com/custom-solutions/

    Reply ↓
  6. Ian Asbury

    You skipped the hard part about how you got your New Mexico's and New Jerseys into one field using space delimited. Between your 2nd and 3rd pic, you magically transform them. Not helpful.

    Reply ↓
      • WONG

        agreed, this solution only works in perfect world scenario, where address is in standardised format, some addresses have multiple address lines like apartment block, unit number and etc. this won't work.

        Reply ↓
    • Dennis

      He has no space between "NewJersey" and "NewMexico", which is how he avoided handling the more difficult logic of dealing with those, as you said.

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.