Results 1 to 7 of 7

data alignment

This is a discussion on data alignment within the Excel Questions forums, part of the Question Forums category; Greetings!! data is as in attached screenshot link at but when pasted as text in excel it appears / pasted ...

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    68

    Default data alignment

    Greetings!!

    data is as in attached screenshot link at





    but when pasted as text in excel it appears / pasted as








    all data is not properly arranged,could anyone help in providing a macro to get the aligned output as in





    Thanks in advance
    Mahesh

    EDIT - Attempted to fix Image links, but the first two appear broken. You might want to try reposting them. FYI - for image links the HTML code is [ IMG ] link [ / IMG ] (No spaces) - Moderator
    Last edited by maheshr68; Mar 11th, 2013 at 09:30 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    12,011

    Default Re: data alignment

    maheshr68,

    You are posting pictures. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

    You can upload your workbook to Box Net,
    sensitive data scrubbed/removed/changed
    mark the workbook for sharing
    and provide us with a link to your workbook.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007, on a PC.

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,393

    Default Re: data alignment

    Please note that even though you posted images, if you have any concern about the IP addresses being public, you should delete them and repost with sample IP's only. If you can't, just PM me and I'll do it for you.

    As for the pasting issues, have you tried methods other than text?
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    68

    Default Re: data alignment

    Greetings!

    please find the link at , secondly, the data inside is freely available for public view and as such there is nothing sensitive about it, have posted the web url inside sheet.

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    12,011

    Default Re: data alignment

    maheshr68,

    Thanks for the workbook, and the link: Free Proxy List - Public Proxy Servers (IP PORT) - Hide My ***!

    If you can manually copy from the web site, and paste the HTML text into Sheet1, you would get this (the country flags are not showing in column D in the screenshot):

    Excel 2007
    ABCDEFGH
    1Last updateIP addressPortCountrySpeedConnection timeTypeAnonymity
    28 secs200.195.44.1583128 BrazilHTTPSHigh +KA
    38 secs183.60.80.12280 ChinaHTTPHigh
    48 secs201.64.247.38080 BrazilHTTPNone
    58 secs95.215.48.1468080 UkraineHTTPSHigh +KA
    68 secs2.133.92.24680 KazakhstanHTTPSHigh +KA
    71m 9s89.28.101.2453128 Moldova, Republic ofHTTPSHigh +KA
    81m 9s187.104.39.498080 BrazilHTTPSHigh +KA
    91m 9s58.215.75.17080 ChinaHTTPHigh +KA
    102m 8s125.160.35.148080 IndonesiaHTTPNone
    112m 8s173.45.108.2503128 United StatesHTTPSHigh +KA
    122m 8s190.12.50.183128 EcuadorHTTPSHigh +KA
    132m 8s82.116.53.1758080 Russian FederationHTTPSHigh +KA
    14

    Sheet1





    After the macro in a new worksheet Results:

    Excel 2007
    ABCDEFGH
    1Last updateIP addressPortCountrySpeedConnection timeTypeAnonymity
    28 secs200.195.44.1583128 BrazilHTTPSHigh +KA
    38 secs183.60.80.12280 ChinaHTTPHigh
    48 secs201.64.247.38080 BrazilHTTPNone
    58 secs95.215.48.1468080 UkraineHTTPSHigh +KA
    68 secs2.133.92.24680 KazakhstanHTTPSHigh +KA
    71m 9s89.28.101.2453128 Moldova, Republic ofHTTPSHigh +KA
    81m 9s187.104.39.498080 BrazilHTTPSHigh +KA
    91m 9s58.215.75.17080 ChinaHTTPHigh +KA
    102m 8s125.160.35.148080 IndonesiaHTTPNone
    112m 8s173.45.108.2503128 United StatesHTTPSHigh +KA
    122m 8s190.12.50.183128 EcuadorHTTPSHigh +KA
    132m 8s82.116.53.1758080 Russian FederationHTTPSHigh +KA
    14

    Results





    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code:
    Option Explicit
    Sub ExtractFromHTMLPaste()
    ' hiker95, 03/11/2013
    ' http://www.mrexcel.com/forum/excel-questions/690485-data-alignment.html
    Application.ScreenUpdating = False
    If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Results"
    With Sheets("Results")
      .UsedRange.Clear
      Sheets("Sheet1").UsedRange.Copy
      .Cells(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      .Cells.EntireColumn.AutoFit
      .Activate
    End With
    Application.ScreenUpdating = True
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ExtractFromHTMLPaste macro.
    Last edited by hiker95; Mar 11th, 2013 at 01:41 PM.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007, on a PC.

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    68

    Default Re: data alignment

    Hiker95, works wonderfully, thanks!! Best Regards, Mahesh

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    12,011

    Default Re: data alignment

    maheshr68,

    Thanks for the feedback.

    You are very welcome. Glad I could help. And, come back anytime.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007, on a PC.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com