none
Macro to extract string between semi colons RRS feed

  • Question

  • 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.



    Friday, June 15, 2012 7:13 PM

All replies

  • 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.



    Macro? You mean for Excel?

    You're in the wrong forum. This is a forum for VB Net developers.


    Please call me Frank :)

    Friday, June 15, 2012 7:17 PM
  • If you are referring to VBScript then I would post your question to The Official Scripting Guys Forum!.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 15, 2012 7:23 PM
  • 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.
    Saturday, June 16, 2012 7:59 AM
  • You could use formulas: with a text string in A1, the formula in B1 could be

    =MID(A1,FIND(";",A1)+1,FIND(";",A1,FIND(";",A1)+1)-FIND(";",A1)-1)

    This can be filled down.

    If you prefer a macro:

    Sub ExtractStrings()
        Const lngCol = 1 ' column with strings; here 1 = column A
        Const lngFirst = 1 ' first row to inspect, here row 1
        Dim lngRow As Long
        Dim lngLast As Long
        Dim arrParts As Variant
        lngLast = Cells(Rows.Count, lngCol).End(xlUp).Row
        For lngRow = lngFirst To lngLast
            arrParts = Split(Cells(lngRow, lngCol).Value, ";")
            If UBound(arrParts) > 1 Then
                Cells(lngRow, lngCol + 1).Value = arrParts(1)
            End If
        Next lngRow
    End Sub

    Change the constants lngCol and lngFirst as needed.

    Regards, Hans Vogelaar

    Saturday, June 16, 2012 8:59 AM
  • An alternative method of doing this is to use Text To Columns.

    Select the column of data -> Text To Columns

    Set Delimited -> Next -> set Semi colon as delimiter -> Next

    Select each column not required and click the option button "Do not import column (Skip)"

    You can either leave the Destination as the default if you want to overwrite the Source data or set it to another column so that the Source data is preserved.

    Click Finish.

    You could record VBA code to do this if you particularly want it in VBA.


    Regards, OssieMac

    Saturday, June 16, 2012 11:07 AM
  • Thanks for your reply Hans. Your macro works perfectly and is just what i was searching for.
    Sunday, June 17, 2012 5:25 PM