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.

 

[Michael M]

Try

Excel 2003
	A	B
1	Aitken.Judoh 47854(40001)	40001
2	Akehurst.Melni da 21447(40953)	40953
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)
B2	=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)

 

[JoeMo]

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

from the bold part above, I get the impression that your data look like this:

Excel Workbook
	A	B
1	Aitken.Judoh 47854(40001) Akehurst.Melni da 21447(40953) Albanese. Teresa (35326)	Employee #'s
2	Alexandria.Deirdre (36098) Allan.Ross (32871)	40001
3	Amold.Brian (34720) Amott.Rosanne (40807) Ascough.lain (35307) Ashton.Helen (35545)	40953
4		35326
5		36098
6		32871
7		34720
8		40807
9		35307
10		35545
Sheet1

If that's the case, you can use the code below to extract the numbers.

Sub sastoka()
Dim lRA As Long, c As Range, S As Variant, i As Long, ct As Long, vOut() As Variant
lRA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = True
For Each c In Range("A1:A" & lRA)
    S = Split(c.Value, ")")
    For i = LBound(S) To UBound(S)
        If InStr(S(i), "(") Then
            ct = ct + 1
            ReDim Preserve vOut(1 To ct)
            vOut(ct) = Mid(S(i), InStr(S(i), "(") + 1)
        End If
    Next i
    Erase S
Next c
If ct > 0 Then
Range("B2", "B" & ct + 1).Value = WorksheetFunction.Transpose(vOut)
End If
Application.ScreenUpdating = True
End Sub

 

[sastoka]

[TABLE="width: 703"]
<tbody>[TR]
[/TR]
[TR]
[TD]Thanks for your quick answer michael but it doesnt work because the cell has more than one employee in it

Abedkhah. Sosan (40000) Abeywardene.Ranjci a (40171) Abeywardene.Ushari (36528) Abraha.Lemlem (36460) Ackroyd.Melni da (40172) Adam.Maureen (40174) Adams.Oaire (36513)

this is the content of my cell A1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks

 

[Rick Rothstein]

Here is another macro you can consider (you may find it interesting given that it does not use any loops)...

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

 

[Michael M]

my apoligies sastoka.....I read this

This what the cloumn A looks like

to mean each cell.
You've got a couple of great resposnes though..

 

[sastoka]

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.

 

[Rick Rothstein]

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

I don't understand why that should be happening to you... I tested the using cell values like what you posted in Message #4 and the code worked perfectly for me. You have working code that Joe gave you, so you are good to go that way, but if you are able to, I would appreciate it if you sent me a copy of your workbook so I can see what there is in your data that stop my code from working. If you can send it, my email address is...

rick DOT news AT verizon DOT net

 

[sastoka]

Here is the link to my file

https://dl.dropboxusercontent.com/u/41044160/testing%20file.xlsx

 

[Rick Rothstein]

Here is the link to my file

https://dl.dropboxusercontent.com/u/41044160/testing%20file.xlsx

Thanks. I'm not sure what was going on with my first code... I know I tested it, and it worked, but you are right that it doesn't work on your data. Here is the code revised to work with your data (still no loops)...

Sub EmployeeNumbers()
  Dim EmpNums() As String
  Application.ScreenUpdating = False
  With Range("B2")
    .Value = ")" & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), "") & "("
    .Replace ")*(", " ", xlPart
    EmpNums = Split(Trim(Range("B2")))
    With .Resize(UBound(EmpNums) + 1)
      .NumberFormat = "@"
      .Cells = Application.Transpose(EmpNums)
    End With
  End With
  Application.ScreenUpdating = True
End Sub