data alignment

maheshr68

Board Regular
Joined
Sep 25, 2009
Messages
68
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:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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?
 
Upvote 0
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.1583128BrazilHTTPSHigh +KA
38 secs183.60.80.12280ChinaHTTPHigh
48 secs201.64.247.38080BrazilHTTPNone
58 secs95.215.48.1468080UkraineHTTPSHigh +KA
68 secs2.133.92.24680KazakhstanHTTPSHigh +KA
71m 9s89.28.101.2453128Moldova, Republic ofHTTPSHigh +KA
81m 9s187.104.39.498080BrazilHTTPSHigh +KA
91m 9s58.215.75.17080ChinaHTTPHigh +KA
102m 8s125.160.35.148080IndonesiaHTTPNone
112m 8s173.45.108.2503128United StatesHTTPSHigh +KA
122m 8s190.12.50.183128EcuadorHTTPSHigh +KA
132m 8s82.116.53.1758080Russian FederationHTTPSHigh +KA
14
Sheet1


After the macro in a new worksheet Results:


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

Thanks for the feedback.

You are very welcome. Glad I could help. And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top