Solved: Find & Replace Letter At End Of Number Only

[Barryj]

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.

[VBA]Selection.Replace What:="NTA", Replacement:="OFF", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False[/VBA]
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]

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]

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]

This approach is courtesy of a forum member Aflatoon.

See if this works for you:
[vba]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[/vba]

[Barryj]

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]

Sure we can add a line more from your post#1.
[VBA]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
[/VBA]

[Barryj]

Thankyou Shrivallabha, that is working fine I will mark it as solved.