Old Skool VB Trickery

Recently I had need to modify some column values in an Excel spreadsheet.

I used the following combination of the InStr and Mid functions to modify values on all rows in Sheet1.

Sub RemoveHashesFromAM()

Dim b
Dim v

Dim lresult As String

For i = 1 To 1124

If InStr(1, Sheet1.Cells(i, 16), ";#") > 0 Then

b = InStr(1, Sheet1.Cells(i, 16), ";#")
b = b + 2

Sheet1.Cells(i, 16) = Mid(Sheet1.Cells(i, 16), b)


End If

Next i

End Sub


And I used the following Replace and Trim functions as necessary

Sheet1.Cells(i, 31) = Replace(Sheet1.Cells(i, 31), ";#", " ")
Sheet1.Cells(i, 31) = Trim(Sheet1.Cells(i, 31))