+ Reply to Thread
Results 1 to 4 of 4

Need help with some data manipulation

  1. #1
    Dan B
    Guest

    Need help with some data manipulation

    Hi,

    I have a Word Document (Office 2003) with lots of names and addresses that I
    need in Excel so I can add them to another list to do a data import.

    The data in Word is like this:

    Joe's Bar and Grill
    1000 Somewhere Dr
    City ST Zip
    Contact: Joe Schmoe
    Phone: 800-000-000
    Fax: 800-000-000
    County: WhoKnows

    If I copy and paste that into Excel, of, it puts it in the same column, each
    line on a row.

    This is how I need it in Excel (in Columns):

    Company Address Contact Phone Fax County
    Joe's..... 1000 S... Joe 800-... 800-. Whoknows

    I need to get it into columns, but I don't want the words Contact, Phone,
    Fax etc to show up next to all the names, phone numbers etc. I hope that
    makes sense.

    So...how do I do it?

    Thanks,

    Dan






  2. #2
    Biff
    Guest

    Re: Need help with some data manipulation

    Hi!

    Maybe this will get you started in the right direction:

    The sample you posted is in the range of Sheet1 A1:A7.

    Enter this formula in B1 and copy down to B7:

    =IF(ISERROR(FIND(":",A1)),A1,MID(A1,FIND(":",A1)+2,255))

    B1:B7 will now look like this:

    Joe's Bar and Grill
    1000 Somewhere Dr
    City ST Zip
    Joe Schmoe
    800-000-000
    800-000-000
    WhoKnows


    Then you can get rid of the formulas by converting those to constants.

    Now, how you proceed depends on whether each group has *exactly* the same
    number of rows of info. Are there empty rows between each group?

    You could use a formula like this on Sheet2 that will transpose the data
    from Sheet1:

    =INDEX(Sheet1!$B:$B,(ROWS($1:1)-1)*7+COLUMNS($A:A))

    Copied across then down.

    That will give you the result you're looking for:

    > Joe's..... 1000 S... Joe 800-... 800-. Whoknows


    Biff

    "Dan B" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I have a Word Document (Office 2003) with lots of names and addresses that
    > I need in Excel so I can add them to another list to do a data import.
    >
    > The data in Word is like this:
    >
    > Joe's Bar and Grill
    > 1000 Somewhere Dr
    > City ST Zip
    > Contact: Joe Schmoe
    > Phone: 800-000-000
    > Fax: 800-000-000
    > County: WhoKnows
    >
    > If I copy and paste that into Excel, of, it puts it in the same column,
    > each line on a row.
    >
    > This is how I need it in Excel (in Columns):
    >
    > Company Address Contact Phone Fax County
    > Joe's..... 1000 S... Joe 800-... 800-. Whoknows
    >
    > I need to get it into columns, but I don't want the words Contact, Phone,
    > Fax etc to show up next to all the names, phone numbers etc. I hope that
    > makes sense.
    >
    > So...how do I do it?
    >
    > Thanks,
    >
    > Dan
    >
    >
    >
    >
    >




  3. #3
    Forum Contributor
    Join Date
    12-14-2005
    Posts
    176
    From Your Example if each of the following items is on its own line like your example:
    Joe's Bar and Grill
    1000 Somewhere Dr
    City ST Zip
    Contact: Joe Schmoe
    Phone: 800-000-000
    Fax: 800-000-000
    County: WhoKnows
    Make sure there is a paragraph return after each line. Your might have to turn-on "SHOW/HIDE" paragraph marks (look for the paragraph symbol on your menu bar. You will also need to remove "Manual Line Breaks" and any "Tabs" that are present. Tab looks like a ->, and manual line break will be a different symbol than a paragraph mark at the end of each line. These are found in the Find and Replace popup menu - then select "MORE" - then "SPECIAL"
    Once the "tabs" and "manual line breaks" are removed:
    Then do a SELECT ALL
    Then on the MENU BAR - Select Table - Convert - Text to Table.
    Then in the "Number of Columns" enter 7
    Then in the "Columns Seperated By" Check "Paragraph Marks" and then Click OK.
    Now everthing should go into X number of rows and 7 Columns.
    Then select one column at a time and in the FIND and REPLACE box - enter what you want to delete and then say ok.
    You will get a message that says "Items in Selection have been replaced - Do you want to continue" select the NO Button.
    Once you have your stuff cleaned-up - Copy the table and paste it into EXCEL. In Excel you might select 7 Cells and Select text as the format - and then paste special - paste as text. Then reformat your cells as necessary.

  4. #4
    Dan B
    Guest

    Re: Need help with some data manipulation

    that is another good idea...would have never thought of that one. Thanks!!

    "wjohnson" <[email protected]> wrote in
    message news:[email protected]...
    >
    > From Your Example if each of the following items is on its own line like
    > your example:
    > Joe's Bar and Grill
    > 1000 Somewhere Dr
    > City ST Zip
    > Contact: Joe Schmoe
    > Phone: 800-000-000
    > Fax: 800-000-000
    > County: WhoKnows
    > Make sure there is a paragraph return after each line. Your might have
    > to turn-on "SHOW/HIDE" paragraph marks (look for the paragraph symbol
    > on your menu bar. You will also need to remove "Manual Line Breaks" and
    > any "Tabs" that are present. Tab looks like a ->, and manual line break
    > will be a different symbol than a paragraph mark at the end of each
    > line. These are found in the Find and Replace popup menu - then select
    > "MORE" - then "SPECIAL"
    > Once the "tabs" and "manual line breaks" are removed:
    > Then do a SELECT ALL
    > Then on the MENU BAR - Select Table - Convert - Text to Table.
    > Then in the "Number of Columns" enter 7
    > Then in the "Columns Seperated By" Check "Paragraph Marks" and then
    > Click OK.
    > Now everthing should go into X number of rows and 7 Columns.
    > Then select one column at a time and in the FIND and REPLACE box -
    > enter what you want to delete and then say ok.
    > You will get a message that says "Items in Selection have been replaced
    > - Do you want to continue" select the NO Button.
    > Once you have your stuff cleaned-up - Copy the table and paste it into
    > EXCEL. In Excel you might select 7 Cells and Select text as the format
    > - and then paste special - paste as text. Then reformat your cells as
    > necessary.
    >
    >
    > --
    > wjohnson
    > ------------------------------------------------------------------------
    > wjohnson's Profile:
    > http://www.excelforum.com/member.php...o&userid=29640
    > View this thread: http://www.excelforum.com/showthread...hreadid=498040
    >




+ 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