0

I'm trying to create a database from this website. Each country has loads of agents, with contact details next to their name (Fax Number and Company name are not required).

The problem is, due to the format of the website, when I copy the data into my Excel sheet each Agent has information spread over 3 or 4 rows.

I need each Agent to have all their information on one row,

eg.(Cells in Brackets)

[ A1 ]       [ B1 ]             [ C1 ]  [ D1 ]                     [ E1 ]  [ F1 ]
GRO Alberti  [email protected]  Bosnia  P Hora K2 A 8 41 Dhjetori  Girana  Tel:+42-79/379 82 86.

I tried copy pasting it all manually, but this takes far too long.

Is there any automatic or quicker way of achieving what I'm trying to do?

4
  • I'd suggest you post this question on Stack Overflow, as this is about programming. However, you should then try first and post a question showing what you tried and why it didn't work. Excel has a nice macro-recorder : you can play manually what you want to do, and look at the generated code.
    – d-stroyer
    Aug 20, 2013 at 13:08
  • Thanks d-stroyer, but I already tried there and my question got put on Hold because it was "off topic" Aug 20, 2013 at 13:55
  • Right, I see it now. The point is that you would need to try first (an automated way) and then ask. You might consider my suggestion with the macro-recorder.
    – d-stroyer
    Aug 20, 2013 at 14:17
  • Also, you could consider breaking-up your problem : 1. Get the data from the web site to a (incompletely) formatted text/csv file ; 2. Use Excel, Python, or whatever to import the data into your excel file. Small questions are easier to answer than big ones.
    – d-stroyer
    Aug 20, 2013 at 14:21

2 Answers 2

0

If you want to use Excel formulas:

  • copy&paste the table into Excel
    • rows in first 2 columns will be merged, but formulas can see the value only in upper-left-most cell of the merged area, others cells appear blank for formulas
  • assuming data start in cell A2, add following formula in cell E2 - new line is entered by Alt+Enter:
    =C2 & IF($A3<>"", "", "
    " & E3)
  • copy formula to appropriate range
  • filter by Name column, excluding (Blanks)
  • hide original Address and Other info columns
  • copy & paste special as Values into a new sheet

illustration of solution before last step

The underlying algorithm is:

  1. start at the bottom row
  2. if Name in 1 row down is not empty, create a new string S containing Address from this row
    else add text from Address in this row to the beginning of S
  3. move 1 row up
0

I'm not sure exactly how you are getting the value of C1, but you should be able to get the source of the page. To do this, press Ctrl+U. This should give you a page written in html. Look for line 128 (or where the table starts, there should be the data you need there and it starts with <table class ....

Copy everything from <table class ... to </table> which is on the same line, near to the end.

Paste this into notepad.

Click Ctrl+H and use Find what: <br /> Replace with: " " (one whitespace) and hit replace all.

Copy everything and paste in excel. You can remove the formatting after that to even out the cell dimensions.

Note that hyperlinks will stay in the data.

EDIT:

To separate the email, tel, mobile and fax will be a little longer, but not that long.

To get the email, use this formula in the next cell in E2:

=LEFT(D2, FIND(" ",D2)-1)

And drag down.

To get the numbers now, it'll be easier to get first all of them separately in column F:

=MID(D2, FIND(" ",D2)+1, LEN(D2))

Now in G, we'll get the Tel, in H we'll get the Fax and in I, we'll get the Mobile.

In G2, put and drag down:

=IFERROR(MID(F2,FIND("Tel:",F2)+4,IFERROR(FIND(":",F2,FIND("Tel:",F2)+4),LEN(F2))-FIND("Tel:",F2)-4),"")

In H2, put and drag down:

=IFERROR(MID(F2,FIND("Fax:",F2)+4,IFERROR(FIND(":",F2,FIND("Fax:",F2)+4),LEN(F2))-FIND("Fax:",F2)-4),"")

In I2, put and drag down:

=IFERROR(MID(F2,FIND("Mobile:",F2)+7,IFERROR(FIND(":",F2,FIND("Mobile:",F2)+7),LEN(F2))-FIND("Mobile:",F2)-7),"")

After that, copy everything and paste as values to remove formulas. Select columns G to I and replace all Tel, Fax and Mobile by nothing through Ctrl+H.

The french functions in order mentioned above:

=GAUCHE(D2, TROUVE(" ",D2)-1)

=STXT(D2, TROUVE(" ",D2)+1, NBCAR(D2))

=SIERREUR(STXT(F2,TROUVE("Tel:",F2)+4,SIERREUR(TROUVE(":",F2,TROUVE("Tel:",F2)+4),NBCAR(F2))-TROUVE("Tel:",F2)-4),"")

=SIERREUR(STXT(F2,TROUVE("Fax:",F2)+4,SIERREUR(TROUVE(":",F2,TROUVE("Fax:",F2)+4),NBCAR(F2))-TROUVE("Fax:",F2)-4),"")

=SIERREUR(STXT(F2,TROUVE("Mobile:",F2)+7,SIERREUR(TROUVE(":",F2,TROUVE("Mobile:",F2)+7),NBCAR(F2))-TROUVE("Mobile:",F2)-7),"")
8
  • Thanks, that has pretty much worked, except I need to separate the information in the "other info" column into 3 columns: Email, Landline, Mobile. Is there a way of doing this automatically by any chance? If not I just have to do it all manually. Aug 20, 2013 at 13:56
  • @user2699532 I added some more stuff that you can use to do it quicker than manually :)
    – Jerry
    Aug 20, 2013 at 14:46
  • Thanks for taking the time to do this, but i'm really sorry an error appears when i enter the first formula "=LEFT(D2, FIND(" ",D2)-1)" It is: #NOM? (My Excel is In french so this is an error message in french. Aug 20, 2013 at 14:56
  • @user2699532 Oh, just replace all the commas in all formulas with semicolons ;. J'espere que cela resoud les erreurs :)
    – Jerry
    Aug 20, 2013 at 16:31
  • Im sorry but that didnt work either for me. I must be coming across as a complete Excel noob, but is there any other possible solution for those equations? If not, dont stress it Ill just do the whole thing manually. Aug 21, 2013 at 10:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .