Warm tip: This article is reproduced from stackoverflow.com, please click

Extract text from the left of "-" but ignore cells that do not contain "-"

发布于 2020-03-27 10:23:43

I am trying to extract some values from a cell.

I have figured out how to extract the value of a cell to the left of "-" but I come across an error when the cell does not have a "-" at all. How do I ignore these cells / error?.

Any help would be much appreciated or ways to improve what I have made.

Also if it helps there are roughly 20000 rows on average.

    Dim r As Long
    Dim Location As Long
    Dim m As Long
    Dim ws As Worksheet

    Set ws = Worksheets("NHBRSummary")

        Set ws = Worksheets("Sheet1")
        m = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
        For r = 2 To m
            Location = InStr(1, Cells(r, 1), "-")
            Cells(r, 2).Value = Left(Cells(r, 1), Location - 1)
        Next

End Sub
Questioner
Brendan-John Martin
Viewed
93
Warcupine 2019-07-03 22:52
        Set ws = Worksheets("Sheet1")
        with ws
             m = .Cells(.Rows.Count, "F").End(xlUp).Row
             For r = 2 To m
                 if InStr(1, .Cells(r, 1), "-") > 0 then
                     .Cells(r, 2).Value = split(.Cells(r, 1).value)(0)
                 end if
             Next
        end with

You set ws to a sheet, then immediately change it, you should remove the first set if you aren't using it.

You can still use left() and location if you would like, this is just another way of doing it. The important part is the if statement using instr()