Concatenate Question.

[JayLee]

I have some tables that I pull off a website using copy and paste. The data however isn't kept together when I paste it. For example:

Cell A1 has: 09/22/2002
Cell B1 has: Mary had
Cell B2 has: a little lamb
Cell B3 has: which ran
Cell B4 has: away.
Cell C1 has: Black
Cell C2 has: (empty cell)
Cell C3 has: (empty cell)
Cell C4 has: (empty cell)
Cell A2 has: 09/24/2002
Cell B5 has: Three little
Cell B6 has: pigs built
Cell B7 has: a house.
Cell C5 has: (empty cell)
Cell C6 has: (empty cell)
Cell C7 has: (empty cell)

The output should be:
Cell A1: 09/22/2002
Cell B1: Mary had a little lamb which ran away.
Cell A2: 09/24/2002
Cell B2: Three little pigs built a house.

Is there preferably a function that I can implement using the concatenate function to tell it concatenate those certain rows? If there's VBA code that would do this, It would also be helpful. Thanks in advance!

 

[PaddyD]

Hi - welcome to the board!

a few quick questions first: what happens if you copy from the website to notepad, then excel (i.e. use notepad to get rid of unnecessary special characters etc). Does it come out any better? What site are you pulling the data from? Is the data in a table? (I.e. might we be able to use a web query to retrieve the data instead).

More info please!

Paddy

 

[JayLee]

Well, I would have to try it with notepad and sees if it removes any extraneous stuff. I'm pulling this off of a financial website (prudential investments). The data is all in a nice spreadsheet format on their site but when you copy it from there to excel, it comes out like the example I previously explained. Thanks!

 

[PaddyD]

post the url of the page / site in question, & have a search on this site for "web query"

Paddy

 

[ThePencilQueen]

Which browser are you using...I had a similar problem with netscape but IE handles the tables easily.