Share via

Extract text before and after specific character with VBA

Anonymous
2016-12-20T01:14:10+00:00

I have a column A with data and I need extract text before character("-") and send to column B and extract  the text after character("-") and send to column C.

Example for that I need:

ColumnA            ColumnB            ColumnC

Tom-Jerry              Tom                    Jerry

I try to run th below code but not work please help

Sub extract()

Dim str1 As String

Dim str2 As String

Dim r As Long

Dim m As Long

Set ws = Worksheets("Sheet1")

m = ws.Range("A" & ws.Row.Count).End(xlUp).Row

For r = 2 To m

str1 = Range("A2" & r).Value & " "

str1 = Left(str1, InStr(str1, " ") - 1) & "-"

str1 = Left(str1, InStr(str1, "-") - 1)

Range("B2" & r).Value = str1

str2 = Range("A2" & r).Value & " "

str2 = Left(str2, InStr(str2, " ") - 1) & "-"

str2 = Left(str2, InStr(str2, "-") - 1)

Range("C2" & r).Value = str2

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2016-12-20T03:07:42+00:00

    Do you need to use VBA?

    Formula in B2: =LEFT(A2,FIND("-",A2,1)-1)

    Formula in C2: =MID(A2,FIND("-",A2,1)+1,999)

    But if you want to use VBA

    Sub extract()

        Dim r As Long, dashpos As Long, m As Long

        Dim ws As Worksheet

        Set ws = Worksheets("Sheet1")

        m = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

        For r = 2 To m

            dashpos = InStr(1, Cells(r, 1), "-")

            Cells(r, 2).Value = Left(Cells(r, 1), dashpos - 1)

            Cells(r, 3).Value = Mid(Cells(r, 1), dashpos + 1)

        Next

    End Sub

    Regards

    Murray

    10+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2016-12-21T23:30:56+00:00

    Try this then.

    It should work with them set as date format as well - if you want to test it uncomment the two lines with

    '.NumberFormat = "yyyy-mm-dd".

    Sub extract()

        Dim r As Long, dashpos As Long, m As Long

        Dim ws As Worksheet

        Set ws = Worksheets("Sheet1")

        m = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

        For r = 2 To m

            dashpos = InStr(1, Cells(r, 1), "-")

            With Cells(r, 2)

                .Value = Left(Cells(r, 1), dashpos - 1)

                .NumberFormat = "General"

                '.NumberFormat = "yyyy-mm-dd"

            End With

            With Cells(r, 3)

                .Value = Mid(Cells(r, 1), dashpos + 1)

                .NumberFormat = "General"

                '.NumberFormat = "yyyy-mm-dd"

            End With

        Next

    End Sub

    Regards

    Murray

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-12-21T13:28:37+00:00

    Hi,

    I use the VBA code.The below is an example

               Column A                           Column B

    12/10/2016 - 12/16/2016        12/10/2016

               Column  C                        Column D

             12/16/2016                            = C-B

    I need change the date format in column B and column C to number because I have a formula in column D to calculate the difference between column C and Column B and I can't change the format in column C

    0 comments
  2. Anonymous
    2016-12-21T04:36:31+00:00

    Can you show an example of what happens now, and an example of what you want it to be?

    Also, are you using the formula solution or the VBA solution?

    Regards

    Murray

    0 comments
  3. Anonymous
    2016-12-21T03:51:47+00:00

    Hi myall,

    I have a little problem when I extract the data because i have dates in some cells and when pulling the date from the right side after dash(-) and send to the column C I can't change the date format to a number and I need this change can you help me with this problem

    Thanks

    Magia

    0 comments