Excel VBA - Append cell by removing string-CodePudding

In my spreadsheet I have added strings after cell values in a range. The cells had values from 0 to >30. Depending on their value they had a string added to them.

For example, values less than 1.7 had " (Very Low)" added. This continued up to >30 where " (Very High)" was added after.

To recap, the cells went from 1.1 to 1.1 (Very Low)

I need to know how to remove the string part of the cell's value because more data will be added and the cell value will be changed. Without removing the string, the cell is still read and another section of string is added to the end. 1.1 (Very Low) (Very Low).

Apologies about no reprex, I'm such a beginner with VBA!

CodePudding user response：

Use left and find:

=LEFT(A1,FIND("(",A1,1)-1)

VBA:

Dim p As String
Dim loc
p = "("

With ActiveSheet
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 1 To lastrow
    loc = InStr(1, .Cells(i, "A").Value, p)
    .Cells(i, "A") = Left(.Cells(i, "A"), loc - 1)
Next

End With

CodePudding user response：

I have figured out how to do this using InStr.

I was trying to edit cells in a range by cycling through them whilst checking for a string. I would then remove the specified string. You can do this by checking the cell for your string using InStr and then using Replace to update the cell contents.

I found a way to do this using:

Dim ws as Worksheet, rCell As Range, rArea As Range

    Set rArea = ws.Range("A1:A10")
    For Each rCell In rArea.Cells
        If InStr(rCell.Value, " (Very Low)") Then
            rCell.Value = Replace(rCell.Value, " (Very Low)", "")
        End If
    Next rCell