Concatenate Question.

JayLee

Board Regular
Joined
Sep 23, 2002
Messages
52
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Sep 23, 2002
Messages
52
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
post the url of the page / site in question, & have a search on this site for "web query"

Paddy
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
Which browser are you using...I had a similar problem with netscape but IE handles the tables easily.
 

Forum statistics

Threads
1,143,742
Messages
5,720,596
Members
422,292
Latest member
Bernd0501

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
Top