PDA

View Full Version : Solved: Find & Replace Letter At End Of Number Only



Barryj
12-01-2011, 10:16 PM
I import data from another source which then gets sent to another sheet for printing, lately the source info has changed slighty and I am wondering if someone could help provide a solution.

Curently I use a find and replace macro to tidy up any little problems, but this is not solving this issue.

Previously the data looked like this:

1373
1372
1385
1368

Now the data coming in looks like this:

1373T
1372T
1385T
1368T

The problem with using the current find and replace macro is that there is other data in the column with words that have a T in them.

What I'm looking for is a solution that only removes the T if it is behind a number and not in a word.

Selection.Replace What:="NTA", Replacement:="OFF", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If I use the above find and replace solution I lose the T out of words that contain a T.

I hope this make sense and appreciate any help.

shrivallabha
12-01-2011, 11:14 PM
Two questions:
1. Is the data in a specific column or spread all over the sheet?
2. Will it always be ****T format (i.e. 4 numbers followed by T)? or the length may vary.

Barryj
12-01-2011, 11:26 PM
Data will only be in column C, and it will only be a T behind the set of four numbers, I hope to incorporate any solution into a macro as the other find and replace works on worksheet activate.

Thanks.

shrivallabha
12-01-2011, 11:45 PM
This approach is courtesy of a forum member Aflatoon.

See if this works for you:
Public Sub ReplaceT()
With Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("=INDEX(IF(MID(" & .Address & ",5,1)=" & Chr(34) & "T" & Chr(34) & ",LEFT(" & _
.Address & ",4)," & .Address & "),0,0)")
End With
End Sub

Barryj
12-02-2011, 12:05 AM
Shrivallabha, thanks that is working except that it is putting a zero in any blank cells, can the code be modified to emliminate this.

Thanks

shrivallabha
12-02-2011, 12:25 AM
Sure we can add a line more from your post#1.
Public Sub ReplaceT()
With Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("=INDEX(IF(MID(" & .Address & ",5,1)=" & Chr(34) & "T" & Chr(34) & ",LEFT(" & _
.Address & ",4)," & .Address & "),0,0)")
.Replace 0, "", xlWhole
End With
End Sub

Barryj
12-02-2011, 12:36 AM
Thankyou Shrivallabha, that is working fine I will mark it as solved.