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

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.



6 thoughts on “Splitting a full address into three or more separate cells in Microsoft Excel

  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.

  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

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>