+ Reply to Thread
Results 1 to 10 of 10

Word / Excel Help

  1. #1
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55

    Word / Excel Help

    Hello all

    Im at work and someone has given me some bad data.

    Its on word and looks like the fllowing

    Ms a Sample
    1 Sample Lane
    Sample Town
    Sample County
    ME34 LALA
    E-Mail: Sample@aol.com

    Ms a Sample
    1 Sample house
    Sample Lane
    Sample Town
    Sample County
    ME34 LALA

    E-Mail: Sample@aol.com



    I have 366 odd addresses where there the address lines range from 4 lines to 6 lines, gaps in data etc.

    I need to get this into excell into a table with

    name | Address line 1 | address line 2 | address line 3 | ETC

    I have tried putting it inot excell deleting blanks then pasting into word, then doing Text to table convert but because the address lines are not spaced the fields come out in the incorrect collums rows etc Ideas>???? Thanks JL
    Last edited by NBVC; 11-24-2008 at 08:51 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    You can copy each address block, and go to where you want to put the result, then go to Edit|Paste Special and select Transpose..

    Repeat for each address block.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55
    Hello, also tried this but the cells are a dirrerect size so it creates an error and refuses to work and doing 366 1 by one is something I dont have the time to do.

    Thanks .... any other ideas?

  4. #4
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55
    Hello, also tried this but the cells are a different size so it creates an error and refuses to work and doing 366 1 by one is something I dont have the time to do.

    Thanks .... any other ideas?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Another way using a formula would be...

    Say your list is in Column A, starting at A2, and each group is separated by an empty row...

    Then in B2, apply this formula: =IF($A1="",INDIRECT("A"&COLUMN(B1)),"") and copy it across 6 columns or more to get the data you need. Then copy them down your list.

    The transposed address should appear next to each first line of each block.

    Now select columns B to the last column and go to Edit|Copy.

    Go to B1 and then Edit|Paste Special and select Values.

    Now you can delete Column A which contains your originals.

    Delete the Blank Rows between

  6. #6
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55
    This just brings back A2 in all of the B collums A3 in all of the C collums ETC

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    See the attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55
    if you change the email of the 1st one it will change the results of the second one lol

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Sorry about that... it was too early in the morning.....

    Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down

  10. #10
    Registered User
    Join Date
    11-21-2008
    Location
    Kent
    Posts
    55
    Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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