Automate formatting excel data onto new worksheet?

How would I take a row of data and automate the process. I have limited experience programming. Here is an example of the raw data I am working with:

Date First Name Last Name Age Address State Zipcode

1/1/10 Jon Doe 25 1 Sun Lane NY 12345

1/2/10 Jane Doe 22 15 Sun Drive NJ 12346


Then I want to take this data and print it out so that each row of data is shown as:

Name: Jon Doe

Age: 25

Address: 1 Sun Lane State: NY Zip Code: 12345


Name: Jane Doe

Age: 22

Address: 15 Sun Drive State: NJ Zip Code: 12346


I know there is a simple way to automate this. I am working with a file that has a couple thousand lines that I need to redo the formatting for and don't know how to automated he process. Any help is greatly appreciated.

1 Answer

  • 10 years ago
    Favorite Answer

    This is a simple but brute force way to do this.

    Insert a new worksheet into your workbook. In this new worksheet, enter these formulas:

    in B4, "=SEARCH(" ",INDIRECT("Sheet1!$A" & INT((ROW()/4)+1)),A4+1)"

    Copy B4 into C4, D4, and E4.

    In F4, "=IF(MOD(ROW(),4)=0,"Name: " & MID(INDIRECT("Sheet1!A" & INT((ROW()/4)+1)),

    $B4+1,$D4-$B4-1), IF(MOD(ROW(),4)=1,"Age: " & MID(INDIRECT("Sheet1!A" &

    INT(ROW()/4)+1), $D4+1,$E4-$D4-1), IF(MOD(ROW(),4)=2, "Address: " &

    MID(INDIRECT("Sheet1!A" & INT(ROW()/4)+1), $E4+1,LEN(INDIRECT("Sheet1!A"

    & INT(ROW()/4)+1))-$E4-6) & " Zip Code: " & RIGHT(INDIRECT("Sheet1!A" &

    INT(ROW()/4)+1),5), "---------…")))"

    If your address list is NOT in Sheet1, then find and replace "sheet1" with the name of your worksheet. If the address list does NOT start in row 2 of that sheet, then find and replace "INT(ROW()/4)+1" with "INT(ROW()/4)+n" where "n" is the row ABOVE the first address line. If the address list is NOT in column "A", then find and replace "!A" with "!x" where "x" is the column where it exists.

    Copy B4 through F4, then press CTRL+G, in "Reference" enter "B9000", click "OK". Press-AND-HOLD SHIFT, tap "END", then tap UP-ARROW. Release keys, then press CTRL+V to paste the formula into the 9000 (about) rows. Adjust the end point to coincide with the amount of addresses you actually have by deleting the extra rows.

    Set your print area to be column "F" on your new worksheet and print.

    • Login to reply the answers
Still have questions? Get your answers by asking now.