+ Reply to Thread
Results 1 to 6 of 6

Excel Genius Needed - Reformatting Addresses

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Excel Genius Needed - Reformatting Addresses

    Hi there,

    I have a list of companies in Excel and a proprietary formula that will pull in addresses for each of these companies. The problem is that the formula outputs the address of each company in three seperate rows and I need the each line of the address to display in a different column.

    For example, the formula outputs:

    5301 Stevens Creek Boulevard
    Santa Clara, CA 95051
    United States

    And I need it to show:
    5301 Stevens Creek Boulevard | Santa Clara, CA 95051 | United States

    I don't know how to insert a macro myself, but if I had one that would insert two extra rows inbetween each company in my list and then would run my formula then another macro to transpose every group of three rows into three columns I think that would work.

    Thanks

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Genius Needed - Reformatting Addresses

    do you want the results all in the same cell or would it be ok for them to be across in 3 cells in the same row?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Excel Genius Needed - Reformatting Addresses

    I'd actually prefer if they were across three cells in different columns in the same row

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Genius Needed - Reformatting Addresses

    you can try this...
    =INDEX($A:$A,(ROWS($B$2:B2)-1)+CEILING(COLUMNS($B$2:B2)/1,1),MOD(COLUMNS($B$:B2)-1,1)+1)&"" in cell B2, then dragged left into C2 and D2.
    should work though it doesn't add the "|" part.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Genius Needed - Reformatting Addresses

    BTW, write it this way if you want all of them to go down row to row, in other words that formula will need to be pasted every third row to get you the results.
    If you don't want it that way but rather you have a street address in row 1, a city / state in row 2, a country in row 3 then repeat for rows 4, 5 and 6 etc, it needs to be this way...
    =INDEX($A$:$A$,(ROWS($B$1:B1)-1)*3+CEILING(COLUMNS($B$1:B1)/1,1),MOD(COLUMNS($B$1:B1)-1,1)+1)&""
    Start in B1 and drag across to C1 and D1, then down and each will appear in each row below.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel Genius Needed - Reformatting Addresses

    Hi financeanalyst,

    Please see the attached file.
    Please note that I created a helper column (A) in order to accomplish your request. The formulas that are used are array formulas and need to be entered with Ctrl+Shift+Enter combination.
    Attached Files Attached Files
    Last edited by AlKey; 10-11-2013 at 02:39 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Genius Needed - Conditional Formatting.
    By themanwithnoshoes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2013, 08:14 AM
  2. genius needed asap!
    By jbaich in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2011, 06:41 PM
  3. Help needed with reformatting text and fill color in cells
    By iluvmyelement in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2011, 08:34 PM
  4. Reformatting addresses
    By nazim in forum Excel General
    Replies: 10
    Last Post: 04-27-2007, 12:50 AM
  5. VBA genius needed... Please help
    By bsnapool in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2006, 10:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1