1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel column to rows - address information

Discussion in 'Business Applications' started by MARES2010, Oct 13, 2010.

Thread Status:
Not open for further replies.
  1. MARES2010

    MARES2010 Thread Starter

    Joined:
    Oct 13, 2010
    Messages:
    1
    In my excel file, I have a vendor name in column A and the address information (street, city/state/zip) in column B. I need to move city/state/zip to column c. The issue is that some vendors have 2 lines of information, some have 3 (apartment number, for example) and some have no address at all. Each record is separated by a blank row though.

    How can I get the address information to go across to columns C and D (if necessary)?
     
  2. col_lou

    col_lou

    Joined:
    Apr 20, 2007
    Messages:
    14
    In a perfect world there should only ever be one piece of data in a column. For example; Column A = Vendor Name, Column B = Street Address, Column C = Street Address 2 (Apt. # , Suite # etc), Column D = City, Column E = State, Column F = Zip. There shouldn't be any blank columns in between.

    That said, there's a function in Excel in the Data menu or ribbon (depending on if you're using 2003 or 2007) called Text To Columns which lets you to break out data from one cell to many cells. So, if you have address/city/state/zip in one cell - you can tell it to put the address in one cell, the city in the next cell, the state in the next cell and the zip in the next cell.

    However - you have to tell Excel how to separate the data in your cell. It can be by a "delimiter" or by counting character spacing. Delimiter means... You can tell it to look for a comma, or a space, or a dash or something that's placed between each piece of data and wherever it sees that delimiter it will put the next piece of information into the following cell. By spacing you're telling it to count a certain number of characters to the right then split the data into the next cell. This probably won't work because address information is all across the board with regard to character counts.

    Address information is difficult with delimiters as well because more often than not the only thing you'll have between each piece of data is a space. That works for the city state zip part, but gets confusing for the street address part because there's usually a space between the street number, street name, street type (Ave., Rd., Blvd.).

    So first sort your records so they don't have any blank rows between them (another no no in Excel). Then highlight the whole column you want to break out. Select Data>Text to Columns then tell it how you want to separate the data. Make sure there's nothing in the cells to the right of the data or they'll be overwritten.

    Hope it works. Like I said - addresses are tough.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/956012

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice