Copy & Paste values in adjacent cell

i had data in html format.i want the data to be splitted into columns in excel.

HTML FORMAT
Employee name
Emp id


johnabraham wesley peter neiljohn and so on....... 254800 254801 254802 254803


i tried copying the data from html to excel. Then applied text to columns but i m getting incomplete names from this step.

My problem is how would i get the full name in one Column(Column B) & the emp id in the next Column (COLUMN C).Column A will have the data copied from html page. The length of the employee name differs. The only saving tip is that the employee name will suffix with sno like

Johnabraham -1
254800

Any function or VBA routine will help me.

Some thing like

Option Explicit
Function ptest(ind) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("vbscript.regexp")

    With RegEx
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "^[a-z]+"
    End With
    If RegEx.Test(ind) = True Then
        ptest = "YES"
    Else
        ptest = "NO"
    End If
    Set RegEx = Nothing
End Function


Sub ptesti()
    Dim b#, y, p, i!, k(), e, d#
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        p = .Value
    End With
    ReDim k(1 To 500, 1 To 2)
    b = 1
    d = 1
    For Each e In Split(p, " ")
        Select Case ptest(e)
        Case "YES"
            k(b, 1) = e
            b = b + 1
        Case Else
            k(d, 2) = e
            d = 1 + d
        End Select
    Next
    Range("e1").Resize(500, 2).Value = k
End Sub

i think i had given the enough requirements.i cant paste or show data due to integrity reasons.sorry for that.

By the way the code by PIKE is not working properly.plz debug it to my usage.

i think i had given the enough requirements.i cant paste or show data due to integrity reasons.sorry for that.

Originally Posted by excelkeechak

By the way the code by PIKE is not working properly.plz debug it to my usage.

How do you suppose somebody do that?

Excelkeechak,

i think i had given the enough requirements.i cant paste or show data due to integrity reasons.sorry for that....

plz debug it to my usage

This kind of texts will not get you a solution fast. These people try to help you in the best way they can. Please be thankfull and make a sample post with a Before situation and an after situation. If the data can't be given due to integrity reasons, post a WB with # fictitious data.

By HTML format i mean the data is imported into excel from web.ok.i m posting the dummy file then.thanks for all those posts

I use internet explorer & firefox???

try..

Sub ptest()
    Dim e, d#, a#
    For a = 1 To 2
        d = 1
        For Each e In Split(Range("A" & a), " ")
            Cells(3 + a, 1 + d) = e
            d = d + 1
        Next
    Next
End Sub