Excel (2010) Macro to extract string between semi colons

Hi,

I am trying to build a macro that extracts a string between semi colons. For example,

Record no. 2551: Unknown combination of numbers. (#40: : BT12345678) (;BT12345678;0)

I would need to extract the string BT12345678 which is between the two semi colons and copy that to the cell next to it.There are many rows of this type down the document, though the needed string is not always in same place (but always between the two semi colons). This macro should run for all the lines till the end of the document. I hope somebody can help me.

Thanks.

try

Sub ert()
Dim x, i&
'On Error Resume Next
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    x = .Value
    For i = 1 To UBound(x)
        x(i, 1) = Split(x(i, 1), ";")(1)
    Next i
    .Offset(, 1).Value = x
End With
End Sub

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Offset(, 1).Value = Mid(.Value, InStr(.Value, ";") + 1, InStrRev(.Value, ";") - InStr(.Value, ";") - 1)
    End With
Next i
End Sub