VBA replace letter and number

[MrTeeny]

I'm trying to remove an occurence of a letter and number from a string, the number may or may not appear in the string, It'll be in the format T1,T2 etc in a string like so

Football T9 something else

I'd just want the string changed to

Football something else

It'd be used in a UDF if that makes any difference, I was basically looking for how to use wildcard for the number as the letter would always be T.

Thanks

 

[mole999]

could you not just do a find replace " T? "

 

[MrTeeny]

could you not just do a find replace " T? "

Thanks unfortunately that doesn't work either , I just need the regex for the numbers , taking the letter out is simple enough using

= Replace(str, " T", "") but the question mark or # don't do anything, I'm guessing they're being taken literally as a string in " T? "

 

[mole999]

= Replace(str, " T", "") is only replacing "space T"

the question mark is a wild card hence why in find replace I was looking at space T and a character followed by space, that may not be what finally works

 

[Rick Rothstein]

Here is a UDF that you can try...

Function NoTnumber(S As String) As String
  Dim X As Long, Words() As String
  Words = Split(S)
  For X = 0 To UBound(Words)
    If Words(X) Like "T#" Then Words(X) = ""
  Next
  NoTnumber = Application.Trim(Join(Words))
End Function

 

[Rick Rothstein]

Here is a UDF that you can try...

Function NoTnumber(S As String) As String
  Dim X As Long, Words() As String
  Words = Split(S)
  For X = 0 To UBound(Words)
    If Words(X) Like "T#" Then Words(X) = ""
  Next
  NoTnumber = Application.Trim(Join(Words))
End Function

The code I posted will work no matter how many digits follow the T. If that number after the "T" is always a single digit, then you can use this formula instead...

=TRIM(REPLACE(A1,FIND(" T"," "&A1&" T"),2,""))


EDIT NOTE: If there can be more than one digit following the "T", then this longer formula should work...

=TRIM(REPLACE(A1,FIND(" T"," "&A1&" T"),FIND(" ",A1&" ",FIND(" T"," "&A1&" T")+1)-FIND(" T"," "&A1&" T"),""))

 

[MrTeeny]

= Replace(str, " T", "") is only replacing "space T"

the question mark is a wild card hence why in find replace I was looking at space T and a character followed by space, that may not be what finally works

I did try

[COLOR=#574123]= Replace(str, " T? ", "") [/COLOR]

but that didn't work , like I said I think it's taking the T? literally as those characters rather than using the ? as a wildcard.

I ended up splitting the string and passing the offending portion thru a replace statement which works fine and is a single line, thanks for the UDF Rick I'll see if I can incorporate it within my current coding