Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA replace letter and number

  1. #1
    Board Regular
    Join Date
    Jul 2017
    Posts
    219
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default VBA replace letter and number

    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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,018
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA replace letter and number

    could you not just do a find replace " T? "
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Jul 2017
    Posts
    219
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA replace letter and number

    Quote Originally Posted by mole999 View Post
    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? "

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,018
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA replace letter and number

    = 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
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,750
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    17 Thread(s)

    Default Re: VBA replace letter and number

    Here is a UDF that you can try...
    Code:
    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's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,750
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    17 Thread(s)

    Default Re: VBA replace letter and number

    Quote Originally Posted by Rick Rothstein View Post
    Here is a UDF that you can try...
    Code:
    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"),""))
    Last edited by Rick Rothstein; Mar 11th, 2018 at 01:15 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Jul 2017
    Posts
    219
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA replace letter and number

    Quote Originally Posted by mole999 View Post
    = 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

    Code:
    = Replace(str, " T? ", "") 
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •