Extract number out of text but only if in bracket.

[sastoka]

Hi everyone,

I received an excle file hat include a list of employe and the employee number.

The problem is that it's multiple employee in the same cell and multiple employee number per employee

However the current employee number in the one in bracket.

I would to extract each employee number in bracket and make a list out of it

Any idea how could I get it done.

This what the cloumn A looks like

Aitken.Judoh 47854(40001) Akehurst.Melni da 21447(40953) Albanese. Teresa (35326) Alexandria.Deirdre (36098) Allan.Ross (32871)
Amold.Brian (34720) Amott.Rosanne (40807) Ascough.lain (35307) Ashton.Helen (35545)

And this would be the result

[TABLE="width: 56"]
<colgroup><col style="width:42pt" width="56"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 56"]40001[/TD]
[/TR]
[TR]
[TD="class: xl65"]40953[/TD]
[/TR]
[TR]
[TD="class: xl65"]35326[/TD]
[/TR]
[TR]
[TD="class: xl65"]36098[/TD]
[/TR]
[TR]
[TD="class: xl65"]32871[/TD]
[/TR]
[TR]
[TD="class: xl65"]34720[/TD]
[/TR]
[TR]
[TD="class: xl65"]40807[/TD]
[/TR]
[TR]
[TD="class: xl65"]35307[/TD]
[/TR]
[TR]
[TD="class: xl65"]35545[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks for your help.

 

[JoeMo]

Many thanks guy,

@joe, yours works perfectly many thanks i'll use it.

@Rick, Thanks for your time. in case you want to know I get the runtime error "13" Type mismatch with this higligh

Sub EmployeeNumbers()     
Dim EmpNums() As String
  Application.ScreenUpdating = False
  With Range("B2")
  [COLOR=#0000ff]  .Value = ")" & (Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value), "")) & "("[/COLOR]
    .Replace ")*(", " ", xlPart
    EmpNums = Split(Trim(Range("B2")))
    With .Resize(UBound(EmpNums))
      .NumberFormat = "@"
      .Cells = Application.Transpose(EmpNums)
    End With
  End With
  Application.ScreenUpdating = Truee 
End Sub

But many thanks to all of you.

You are welcome - thanks for the reply.