CODEWITHSUNDEEP
excelvba
CustomX
CustomX

Reputation: 10113

Excel VBA - remove certain piece of string

I previously asked how I could check whether a piece of text contains a string, now I would like to delete that piece of string if it is found in that cell.

If the value in column C is different from column W, I check whether column A contains "MIG", if it does I add it to the cell, if not I do nothing.

Because this macro is run everytime you save, if you were to edit the values in W & C to make them match then column A will still say "MIG".

So when the document is saved, the macro will now see column C & W are the same and if it contains "MIG" it should delete "MIG" from the cell.

Sample

If shtVO.Cells(Row, 3).Value <> shtVO.Cells(Row, 23).Value Then
  If shtVO.Cells(Row, 1).Value Like "*MIG*" Then
  Else
    shtVO.Cells(Row, 1).Value = shtVO.Cells(Row, 1).Value + "MIG"
  End If
Else
  If shtVO.Cells(Row, 3).Value = shtVO.Cells(Row, 23).Value Then
    If shtVO.Cells(Row, 1).Value Like "*MIG*" Then
        .....              
    End If
  End If
End If

Upvotes: 1

Views: 5584

Answers (3)

CustomX
CustomX

Reputation: 10113

If shtVO.Cells(Row, 1).Value Like "*MIG*" Then
    shtVO.Cells(Row, 1).Replace What:="MIG", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End If

This works too :)

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53166

Not sure I understand your logic, but to clear a cell value use

shtVO.Cells( ... ).ClearContents

EDIT

Use of Replace

shtVO.Cells( ... ).Value = Replace(shtVO.Cells( ... ).Value, "MIG", "")

The rest of your comment deals with the possibility that you are left with double space after replaceing MIG
Eg "Fisrt MIG second" --> "Fisrt  second"

Upvotes: 2

Kovags
Kovags

Reputation: 540

You can use the Replace() function:

If shtVO.Cells(Row, 1).Value Like "*MIG*" Then
   shtVO.Cells(Row, 1)=Replace(shtVO.Cells(Row, 1),"MIG","")
End If

Upvotes: 1

Related Questions