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

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


  • 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.




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




  • 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




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.

Users are saying about us...

  1. 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.

      • 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.

    • 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.

  2. 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!

Leave a Reply to Dennis Cancel reply

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

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube