• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and replace last two characters

In column D, the values for the first name field end with " ?", i.e. a space followed by a character. I am trying to do a find/replace on these last two characters.

I tried * ? under the Find field and that didn't work.

Is there a way to replace these last two characters?
 
Cannot see any characters beyond the name itself. When I do a check of the length, ie. =Len(Text) it returns the length of the visible characters. Can not replicate your issue.
 
If I am understanding you correctly, then the following VBA will solve.

Code:
Option Explicit

Sub ReplaceTwo()
    Dim i As Long, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For i = 4 To lr
        Range("D" & i) = Right(Range("D" & i), 2)
    Next i
End Sub
 
if there is a space before your desire result

REPLACE(D4,1,FIND(" ",D4)-1,"")

regards
Naresh
 

Attachments

  • Replace last two characters (3).xlsx
    9 KB · Views: 2
In column D, the values for the first name field end with " ?", i.e. a space followed by a character. I am trying to do a find/replace on these last two characters.

I tried * ? under the Find field and that didn't work.

Is there a way to replace these last two characters?
Your method works for me to return
Bill
Kay
in cells D4, D5 respectively. Is this what you are trying to achieve?
 
Your method works for me to return
Bill
Kay
in cells D4, D5 respectively. Is this what you are trying to achieve?

Yes, I am trying to get 'Bill' and 'Kay' but when I do the Find & Replace, but the result I get is a blank cell.

I had to modify my sample file again. Column F has the desired result. So I am trying to apply and Find & Replace on column D. The result would look like column F.
 

Attachments

  • Replace last two characters (3) (2).xlsx
    8.8 KB · Views: 3
Check if it is this way

Decio
 

Attachments

  • Replace last two characters (3) (2).xlsx
    9.2 KB · Views: 5
Since you have posted several different scenarios, here is the latest VBA solution

Code:
Option Explicit

Sub ReplaceTwo()

    Dim i As Long, lr As Long

    lr = Range("C" & Rows.Count).End(xlUp).Row

    For i = 4 To lr

        Range("D" & i) = Left(Range("D" & i), InStr(Range("D" & i), " "))

    Next i

End Sub
 
I'm trying to find a solution that involves using the Find & Replace dialog box, no formulas. Is this possible?

Not unless you are willing to type the whole find for each line and then type the replace for each line. Find and Replace is typically used for a bulk replacement of the same text/value. You will either need to use a formula, VBA or Power Query.
 
Back
Top