try the code

Code:
--------------------

Sub test()
Dim a, b, i, ii
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("a1", Range("a65536").End(xlUp))
Set rng2 = Range("b1", Range("b65536").End(xlUp))
ReDim a(1 To rng1.Count, 1 To 2): ReDim b(1 To rng2.Count, 1 To 2)
For i = 1 To rng1.Count
a(i, 1) = rng1.Item(i).Text
Next
For i = 1 To rng2.Count
x = InStr(rng2.Item(i), Chr(32))
b(i, 1) = rng2.Item(i)
If x 0 Then b(i, 2) = Left(rng2.Item(i), x - 1)
Next
For i = LBound(b, 1) To UBound(b, 1)
If Not IsEmpty(b(i, 1)) Then
For ii = LBound(a, 1) To UBound(a, 1)
If b(i, 2) = a(ii, 1) Then a(ii, 2) = b(i, 1)
Next
End If
Next
Range("a1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=387363