Excel vba find and replace at end

Question

I have to replace the variant at end only. can we do that if so please can anyone help on this?

code:

Worksheets("Part_Number").Columns("F").Replace What:="-G", Replacement:=" ", SearchOrder:=xlByColumns, MatchCase:=True

Input:

WA-2001-G 
WA-GSK024

Expected Output:

WA-2001 
WA-GSK024

Take the time to respond and give "Credit" to previous posts here before coming up with new ones http://stackoverflow.com/questions/43376774/vba-to-create-new-sheet-with-file-name-using-save-dialogue-box-and-save-as-csv-f and http://stackoverflow.com/questions/43354108/how-to-assign-the-property-to-checkbox — Shai Rado, Apr 17 '17 at 03:50
I'm going to be more forthright than @ShaiRado: 6 questions without accepting an answer ... and in your last question you couldn't even be bothered to do the simple search that someone suggested! Why on earth would anyone want to help you? — Ambie, Apr 17 '17 at 04:07

0m3r · Accepted Answer · 2017-04-18T17:21:19.993

2

On your What:="-G" you need to add "space -G space"

Assuming that's what your trying to do

Example here

Option Explicit
Public Sub Example()
    With ThisWorkbook.Sheets("Part_Number").Range("F:F")
        .Replace " -G ", " ", xlPart
    End With
End Sub

Example
WA-2001 -G WA-GSK024
To this
WA-2001 WA-GSK024

Edit Use this if there is no space

Option Explicit
Public Sub Example()
    Dim rng As Range

    With ThisWorkbook.Sheets("Part_Number")
        For Each rng In .Range("F1", Range("F99").End(xlUp))
            If Right(rng.Value, 2) = "-G" Then
                 Debug.Print rng.Value ' Immediate Window
                rng.Value = Mid(rng.Value, 1, Len(rng.Value) - 2)
                 Debug.Print rng.Value ' Immediate Window
            End If
        Next
    End With
End Sub

edited Apr 18 '17 at 17:21

answered Apr 17 '17 at 04:06

0m3r

12,286
15
35
71

Thanks for your reply @ 0m3r – DINESHKUMAR PALANISAMY Apr 17 '17 at 04:30
my requirement is at end of the word it has to find and replace -G but if i use the above it replaces even if it is in middle. – DINESHKUMAR PALANISAMY Apr 17 '17 at 04:32
my inputs are 1. WA-2001-G (THIS IS ONE INPUT) – DINESHKUMAR PALANISAMY Apr 17 '17 at 06:28
WA-GSK024 (THIS IS IN ANOTHER INPUT). In this both if -G exists at end then it has to be removed. so my expected out put for first is WA-2001 & for second will be same (i.e., WA-GSK024) @ 0m3r – DINESHKUMAR PALANISAMY Apr 17 '17 at 06:30
1

In the first sample input "WA-2001-G ", you have a space in the end. Is that correct or a typo? – Subodh Tiwari sktneer Apr 17 '17 at 06:50

score 2 · Answer 2 · answered Apr 17 '17 at 07:49

One way of doing this is by looping Column F and checking last two characters in each cell for -G and remove if exists. See the code below:

Public Sub Demo()
    Dim lastRow, i As Long
    Dim myString As String
    Dim c As Range
    'get the last row with data in Column `F`
    lastRow = Cells(Rows.Count, "F").End(xlUp).Row

    For Each c In ThisWorkbook.Sheets("Sheet1").Range("F1:F" & lastRow)
        If Len(c.Value) <> 0 Then
            If Right(c.Value, 2) = "-G" Then c.Value = Left(c.Value, Len(c.Value) - 2)
        End If
    Next c
End Sub

Let me know if anything is not clear.

thanks for your help this code also works and fulfill my requirement @ Mrig — DINESHKUMAR PALANISAMY, Apr 17 '17 at 09:30

score 1 · Answer 3 · answered Apr 17 '17 at 09:07

1

Or you may try something like this...

Sub ReplaceStrings()
    Dim ws As Worksheet
    Dim lr As Long
    Set ws = Sheets("Part_Number")
    ws.Activate
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    Range("F2:F" & lr).Value = Evaluate("If(Right(Trim(F2:F" & lr & "),2)=""-G"",Left(Trim(F2:F" & lr & "),Len(Trim(F2:F" & lr & "))-2),F2:F" & lr & ")")
End Sub

answered Apr 17 '17 at 09:07

Subodh Tiwari sktneer

9,906
2
18
22

I misunderstood the op question:-) – 0m3r Apr 17 '17 at 09:37
2

I understand. Requirement was not very clear initially but you tweaked it as per the OP's requirement in the end. :) – Subodh Tiwari sktneer Apr 17 '17 at 09:39

Excel vba find and replace at end

3 Answers3