excel data cleansing 2

[vijayleads]

Hi,

I need some help to cleanse data in an excel spreadsheet. Let me explain what i am trying to do. I am getting data from a html page by copying and pasting on to excel. The only problem is that when i convert the text to columns i cannot do it in one shot.

Here is an example of the data.

Name Age Phone
Amar Khanna 18 5478687
Vijay 29 4859749


Above as you will notice the last name is missing for the second row and so if i use "space" as a delimiter to convert the data to columns i have trouble lining up data where all the fields are not present.



Another help that will be greatly appreciated is, when i get the above data from web pages, each of the rows on the web page has a link which gives detail for the specific row. It is practilly impossible for me to go to each web page manually to pull in details. Is there some way that I can write some code within excel that will read the url next to a specific row and go and fetch the detail fields that I want it to.

Any help will be greatly appreciated.

thanks in advance.

vj

 

[xlhelp]

Vijay,

There is no easy about it. Basically, you would do your text to columns where Khanna would fall under column B and so would number 29.

What you would need to do then is to run a macro similar to one below that tests if the value in column B is text or not.

For this particular macro you would highlight all the data in column B and then run the macro.

Sub inscels()
Dim cc As Range

For Each cc In Selection
If Application.IsText(cc.Value) = False Then
cc.Insert Shift:=xlToRight
End If
Next cc

End Sub "Age is a high price to pay for maturity."

 

[vijayleads]

thanks for your help.

i was wondering if you could also help with the second issue i have that of going to the url's and extracting data.

when i get the data from web pages, each of the rows on the web page has a link which gives detail for the specific row. It is practilly impossible for me to go to each web page manually to pull in details. Is there some way that I can write some code within excel that will read the url next to a specific row and go and fetch the detail fields that I want it to.

 

[xlhelp]

Vijay,

I am sorry, I don't know. And don't have time at the moment to find out. You will have to rely on others or post a question in the VBA forum. "Age is a high price to pay for maturity."